When you say vary automatically, I assume you mean you want the column
index to increment as you drag the formula to copy it.
If you're copying it along a row, across columns, you can use the name of
the columns, which will automatically increment as you copy.
By this I mean
Column A = 1
Column B = 2
Column C = 3 ...etc.
*Not* the actual address of the column containing the data.
For example, revise,
=VLOOKUP($R$1,$J$1:$Q$25,2,0)
TO:
=VLOOKUP($R$1,$J$1:$Q$25,COLUMN(B1),0)
Where you wish to copy across the columns.
If you were going to copy the formula down a column, just replace the column
name with a row name:
=VLOOKUP($R$1,$J$1:$Q$25,ROW(A2),0)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
Is there a way to vary the Col Ref automatically in a vlookup?
