VLookup: automatic column changes

[aallaman]

Please Help!

I am working with a worksheet in which I have a vlookup function that needs to be drag and copied 120 columns across. At each change in column, I need the column index number, in the vlookup function, to increase by one.

=vlookup($a1,B:CD,1,false) =vlookup($a1,B:CD,2,false) =vlookup($a1,B:CD,3,false)

Does anyone know of a way to write the formula so that the col_index_num changes automatically when you copy it across columns?

Do I need to use {}? What are {} for?

Thanks!!!!!!1

 

[jbeaucaire]

Use this in the first cell, then copy to the right:

=VLOOKUP($A1, $B:$CD, COLUMNS($A:A), FALSE)

 

[barry houdini]

Hello aalaman, you can use COLUMNS function in place of the column index so that it changes as you drag across but possibly better to use INDEX/MATCH, i.e. this formula copied across

=INDEX(B:B,MATCH($A1,$B:$B,0))

Note the MATCH function won't change as you drag across but the INDEX column will.....

 

[de909]

Here are a couple of options: