Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

enter image description here

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

share|improve this answer
    
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. –  Ellesa 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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.