Dragging a vlook up across a workbook

[Wilgoss]

Is it possible to drag a vlookup from left to right across a workbook so that the formula retains the Lookup_value and table_array but
allows the Col_index_number to increase

I seem to be able to copy it but not increase the col index no!

Thanks in advance for any help

Paul

[tom1977]

You can use function Column() as a number of column

[arthurbr]

You can replace the column index by a formula containing the column() function.

Say you formula is in col B and you want values from the second column, use COLUMN().
When dragging across this value will increase

[romperstomper]

More efficient would be to use a MATCH formula in one column to do the lookup once, then refer to that value in several INDEX formulas, which can then easily be filled across.

[dilipandey]

Hi Paul,

It depends on the data arrangement as which option is best suited for you.
To retain the lookup value and table array, you need to put $ sign before and after these references as this will freeze these two things and column index value will be moved as you move.
You can use Match with Vlookup if you need to assign column index basis on some headers which are arranged differently on source and target data
You can use count or column indicator with vlookup if you have common heading arrangement.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Wilgoss]

Legends - everyone of you! Thanks as usual

Paul