Column index in Vlookup

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

Khalil Handal

Hi,
I have a lot of vlookup formulas that looks like this one:
Cell E21=VLOOKUP($A$3,Marks!$F$14:$DG$65,30,FALSE)
Cell E22=VLOOKUP($A$3,Marks!$F$14:$DG$65,36,FALSE)

The difference of 6 is for the next formula.
When I do some changes in the sheet Marks the column index is changed and I
have to do it manualy.
Is there a way that this can be done automatically? (ie. add 6 each time)
this makes it more easy to work. I copy the cell E21 down for the other
formulas and the number are changed 30, 36, 42 ...
 
Instead of putting 30, 36, 42 etc in the formulae explicitly, you can
calculate what it should be from the row that it is on. Here's an
example which works in a cell on row 21:

=(ROW()-20)*6+24

or, better still:

=ROW(A1)*6+24

(better because it is independent of the row you put it on)

This will return 30. If you copy it down it will return 36, 42 etc on
successive rows. So, just replace your first 30 with ROW(A1)*6+24 in
the formula in E21. Your formula might also become a bit easier to
read if you define a named range for Marks!$F$14:$DG$65 (eg call it
table), then your formula becomes:

=VLOOKUP($A$3,table,30,FALSE)

or with my suggestions above:

=VLOOKUP($A$3,table,ROW(A1)*6+24,FALSE)

Note that if you move this formula onto a different row (eg by
inserting rows above it), then it should still work.

Hope this helps.

Pete
 
Hi Pete,
It cannot be more clear. Thanks a lot. I understood it and it works fine.
 
Back
Top