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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

An alternative solution is creating a User-defined Function, which should make the process very easy.

Here's one approach, which creates a function called XLOOKUP:

Public Function XLOOKUP(ByRef row As Variant, ByRef column As Variant, ByRef table_array As Variant) As Variant
    XLOOKUP = CVErr(xlErrNA)
    Dim xRow As Long, xCol As Long
    With table_array
        For xRow = 1 To .Rows.Count
            If .Cells(xRow, 1).Value = row Then
                For xCol = 1 To .columns.Count
                    If .Cells(1, xCol).Value = column Then
                        XLOOKUP = .Cells(xRow, xCol).Value
                        Exit Function
                    End If
                Next
                Exit Function
            End If
        Next
    End With

End Function

Once you add it to your project, you can use it like this:

=XLOOKUP(RowLabel, ColumnLabel, Range)

Answer 4

You can also simply set your original vlookup using the COLUMN() function for the column with the value to return. A simple version has your table's lookup value column in Column A and the value to return is in any other column, Column L say. Then use:

COLUMN(L:L)

for the value to return's column. More complicated, as in the lookup column is not Column A? Just subtract from the above: (for lookup in Column C)

COLUMN(L:L)-2

The value here is that adding and deleting columns does not break your formula as Excel automatically fixes it. Add a column? It becomes COLUMN(M:M). And so for deleting a column. All handled by Excel. And it is truly easy to do. Columns like "WYO"? Excel doesn't care. Find the column number of your lookup column with a quick COLUMN() formula in that column, then you have the value to subtract. Easy.

No doubt less powerful than Index/Match and practice makes perfect there so doing this holds you back. But it is a quick result that lasts. And you can teach it quickly to others while you hone your Index/Match skills. It is also not as intuitive as using Named Ranges, but many times you cannot (Tables anyone?). (And it can be the underlying value for the Named Range, for that matter.)

So, if you want a quick, simple, hard to break with simple column changes (it will keep its reference if cut and reinserted else-column, rather than relatively look from its new position, but for this kind of work, I think that's a definite plus), then this is a solution to think on.

Answer 5

If you name the column ranges in your lookup formula the formulas will still work fine when you add or delete columns.