keeping col_index_num relative in a vlookup formula

[ddev]

I am using a simple vlookup formula in worksheet A to find values from worksheet B. in my formula, my table array contains absolute cell range (i.e. $A$6:$z$26). However, if I add a row in my worksheet B, then my results get screwed up because my col_index_num in my formula is now off by the number of rows I inserted into worksheet B. Is there a way to automatically change the col_index_num when I modify the data table the formula is pulling from?

[Andrew-R]

Give your range on worksheet B a name by selecting the entire range and then typing a name into the Name box at the top left of the screen, just below the tool bar, e.g. SearchRange

Then change your VLookup formula, replacing $A$6:$z$26 with SearchRange, or whatever you called it.

Edited to add: Sorry, I just looked at your post again, and I'm a bit confused - you're asking about the column return reference, which shouldn't be affected by inserting rows.

If you're actually inserting columns, which will make a difference, then simply naming the range won't solve the problem.

The only way I can think of doing it via formula is to add a header row to your table (say in Row 4) and giving each column a header. Then, in the row below the header have a column reference, starting at 1 for the first column and incrementing by 1 for each column. I'd do this by just putting the formula =COLUMN() in each column.

Then select the header row and the column reference row and give them a name, say HeaderRow

So if, for example, column 1 was "Forename", column 2 "Surname", column 3 "Job" and so on, you'd write your VLOOKUP to say:

=VLOOKUP(SearchRange,ValueToFind,HLOOKUP(HeaderRow,"Job",2,FALSE),FALSE)

That should allow you to insert either rows or columns without having to change the VLOOKUP.

[ddev]

I tried it, but it did not seem to help. My problem is that now, instead of being $A$6:$z$26, it becomes $a$6:$z$30, and now instead of needing the content of the cell in the 15th row (i.e. col_index_num 15), I need the content of the cell in the 19th row.
So my formula first reads VLOOKUP(a1,'worksheetB'!$a$6:$z$26,15) but when I add the rows to worksheet B, the formula automatically updates to VLOOKUP(a1,'worksheetB'!$a$6:$z$30,15) - even though the table array automatically updates, the index (15) does not automatically change to 19. Is there a way to make it do that?

[Andrew-R]

Yes, the bit I added to my post will work, but it's a little bit of a faff on.

[ddev]

by the way, sorry - I mean HLOOKUP - .....

[romperstomper]

Use INDEX and MATCH instead:
INDEX($a$20:$z$20,,match(a1,'worksheetB'!$a$6:$z$6))

[Andrew-R]

Here's the formula in an example sheet.

As you'll see inserting a column into the range on sheet 2 doesn't alter the result of the VLOOKUP in cell A2 on sheet 1.

[ddev]

Can you elaborate a bit? How would index and match work?

[romperstomper]

Per your example, it matches A1 in worksheet B range A6:Z6 to get a column number, then returns the value from row 20 in that column.

[Andrew-R]

I think the problem is that if a row is inserted then the row reference changes, so you can't use an absolute row (i.e. 20) to reference it.

I've turned my last spreadsheet around to use HLOOKUP instead of VLOOKUP, and this will work for everything except a new row being inserted in Row 1.

[romperstomper]

If you insert a row above row 20, the formula will automatically adjust to use row 21.

[Andrew-R]

Sorry, I'm being dense ... how does it know which row to return?

If I was doing an HLOOKUP command I'd have HLOOKUP(search value, search range, row to return, T/F exact match)

So where, in the INDEX and MATCH is the equivalent to the row to return?

My apologies if I'm being thick here, but I've really never used INDEX and MATCH before.

[romperstomper]

Breaking the formula down:
match(a1,'worksheetB'!$a$6:$z$6)
returns a position where A1 is found in A6:Z6 on worksheetB. e.g. if it's found in D6, the MATCH formula will return position 4.
The INDEX part then becomes:
=INDEX($a$20:$z$20,,4)
which means return the value in the fourth column of A20:Z20 - in other words the value in D20.
Make sense?

INDEX and MATCH is much more efficient than HLOOKUP and VLOOKUP generally because it only references the 2 rows or columns that you are actually interested in, not an entire table. It also has the benefit that if you want to return multiple values for the same lookup value, you can put the match formula in a separate column and refer to it directly in multiple INDEX formulas. I've attached a demo workbook.

[Andrew-R]

Thanks for the explanation - I'll just have to find an excuse to use a MATCH/INDEX now.