Excel – How to prevent VLOOKUP from breaking after I add a column to a named range

microsoft excelnamed-rangesvlookup

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.

Best Answer

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

Related Solutions

Excel – Shortest Method To Reference A Single Row/Column of a Named Range in Excel

Row:

INDEX(UnpivotSource,1,0)

Column:

INDEX(UnpivotSource,0,1)

Note that this will only work for contiguous named-ranges, there's an index function for areas of a range, but it could get tricky if you wanted something other than the first row or column.

Can the Name of a Named Range Be Dynamic in Excel? – How to Guide

This assumes that the value in A1 will be entered rather than set by a formula. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim str As String
    str = Range("A1").Text
    If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If n.RefersTo = "=Sheet1!$A$2:$A$4" Then
            n.Delete
        End If
    Next n
    ActiveWorkbook.Names.Add Name:=str, RefersTo:="=Sheet1!$A$2:$A$4"
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

EDIT#1:

to use A1 & B1 as the Name, just replace:

str = Range("A1").Text

with:

str = Range("A1").Text & Range("B1").Text

Best Answer

Related Solutions

Excel – Shortest Method To Reference A Single Row/Column of a Named Range in Excel

Can the Name of a Named Range Be Dynamic in Excel? – How to Guide

Related Question