Is there a way to lock the column index number in VLOOKUP across sheets?

lemery

I'm performing a VLOOKUP across sheets, which works great until I add a column to the source sheet, thereby shifting the column index number. The formula retains the original column index number, so the output ends up being wrong.

EvermoreCoffee

Hi @lemery,

I've run into this issue in the past and swapped out using VLOOKUP for an INDEX/MATCH combination, in order to have flexibility in where the column index is situated. If you want to test it out, the format for cross-sheet referencing is:

=INDEX({range containing value(s) you want} , MATCH([row used in lookup]@row , {range where lookup value is}, sort_value))

Where sort_value is (1 = ascending, 0 = unsorted). So something like:

=INDEX({Book Prices}, MATCH([Book Name]@row, {Book Names}, 0)) on a sheet like this, where [Book Name]@row is "Name 1":

| Book Name | Book Price |

-----------------------------

 Name 1    $ 1.00

 Name 2    $ 2.00

It will look in the {Book Names} range, find the price from the {Book Prices} range at "Name 1" and return the first price it finds for "Name 1". If the columns change positions, it will still return the price we are looking for.

Hope this helps!

lemery

Thanks so much! I hadn't thought about switching to an Index/Match, but that makes sense. Appreciate your help!