How do I prevent VLOOKUP from breaking after I add a column to a named range?

My Excel worksheet contains a lot of VLOOKUPs on a named range that I have defined. Now, when I added a column in the middle of my named range, the VLOOKUPs that reference columns after the inserted column are now broken. I understand the problem, but what is the best way to fix? Is there a way to figure out the column number from the header text?

[EDIT] Using Kaze's idea of INDEX and MATCH seemed to be the best solution. Here's what I ended up with:

``````INDEX(MyTableData, MATCH("RowLabel", RowList, 0), MATCH("ColumnLabel", ColumnList,0))
``````

where MyTableData is a named range of the entire table, RowList is a named range of the row header, and ColumnList is a named range of the column headers.

-

Personally, I prefer using an `INDEX-MATCH` combo for lookups instead of `VLOOKUP`. Here's an example off the Pokedex I was compiling for my niece.

To get Squirtle's Hidden Ability, I'd use this formula:

``````=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1)
``````

This yields the same result as:

``````=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0))
``````

One good thing about INDEX-MATCH is that in most cases, you don't need to reference the entire data range, so the first formula should work even if you regularly add columns and rows to your data range. It also has another advantage: since you're referencing only 2 one-dimensional ranges, it calculates faster.

Nevertheless, you can still use `MATCH` with your `VLOOKUP` formulas to get the column number.

``````=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",\$A\$1:\$H1,0),0)
``````

where:
`\$A\$1:\$H\$1` is the first row in your data/named range, or the row that contains header text
`NAMED_RANGE` is the name for your data range
`"COLUMN_TEXT"` is the header text for the column that contains the data you need
`C1` contains your lookup value

Edit: Added Index-Match example as per Doug's request. :D

-
While I was typing out my answer, you had already sent yours. My goal was to show the OP how to use the `match` function to suit his specific needs. Please feel free to down-vote should my answer be erroneous. – Kaze Aug 11 '11 at 19:58
The suggestion to use Index-Match is the best solution to this issue. I think you should add an example to your answer and make the world a better place. – Doug Glancy Aug 12 '11 at 15:22
Works in Google Sheets as well, thanks! – Red2678 Jun 13 at 0:56

You could use the `MATCH` function:

This site has a pretty good explanation of how to use it:
http://www.techonthenet.com/excel/formulas/match.php

An alternative is to use the `COLUMN` function to read the actual column number (if your table starts from anywhere other than column A, you'd need to subtract off the number of the first column in your formula). So, example:

`````` =VLOOKUP(B2,\$B\$2:\$D\$300,COLUMN(\$D\$1)-Column(\$B\$1), False)
``````

Where column D contains your lookup value

-