Exceljet

Quick, clean, and to the point

VLOOKUP with variable table array

Excel formula: VLOOKUP with variable table array
Generic formula 
=VLOOKUP(A1,INDIRECT("text"),column)
Summary 

To look up a value based on a variable table, you can use the VLOOKUP function together with the INDIRECT function. In the example shown, the formula in G5, copied down, is:

=VLOOKUP(E5,INDIRECT("vendor_"&F5),2,0)

where vendor_a (B5:C8) and vendor_b (B11:C14) are named ranges or Excel Tables. As the formula is copied down, it returns a cost for each color using the vendor in column F to dynamically assign the correct table.

Explanation 

In this example, the goal is to set up VLOOKUP to retrieve costs based on a variable vendor name. In other words, we want a formula that allows us to switch tables dynamically based on a user-supplied value. There are two cost tables in the worksheet, one for Vendor A and one for Vendor B. Both tables are defined as the named ranges vendor_a (B5:C8) and vendor_b (B11:C14).

At the core, this is a basic lookup problem, and we could use the VLOOKUP function to get the cost for a color like this:

=VLOOKUP(E5,"vendor_a",2,0) // vendor a cost for red
=VLOOKUP(E5,"vendor_b",2,0) // vendor b cost for red

These formulas work fine, but the table name provided to VLOOKUP is hard-coded, not variable.

In thinking about how to make the table variable, notice the table names are identical except for the last letter ("a" or "b"). This means we can assemble the correct table for each vendor with concatenation like this:

="vendor_"&"a" // returns "vendor_a"
="vendor_"&"b" // returns "vendor_b"

And, since "a" and "b" are already in column F, we can pick up that value directly:

="vendor_"&F5 // "vendor_a"

The above expression will correctly create the vendor name we need to perform a lookup. However, the formula below will fail with a #VALUE! error:

=VLOOKUP(E5,"vendor_"&F5,2,0) // returns #VALUE!

Why is that? The formula above fails because Excel interprets the table as a text value, not a range. What we need is a way to tell Excel to interpret the text value like a cell reference. This is a job for the INDIRECT function which is designed to evaluate a text value as a reference. Once we wrap the original expression in INDIRECT, we'll get a proper reference:

=INDIRECT("vendor_"&F5)
=INDIRECT("vendor_a)
=B5:C8

Carrying these changes into the final formula, we have:

=VLOOKUP(E5,INDIRECT("vendor_"&F5),2,0) // returns 9.95

Now VLOOKUP will correctly look up the cost for Vendor A or B, depending on the letter entered in column F. In the worksheet as shown, the formula returns $9.95, since the cost for Red from Vendor A is $9.95 . If the vendor is changed to "b", VLOOKUP will dynamically switch tables and return $12.50.

With the IF function

The example above is a nice illustration of the power of setting up a worksheet with consistently named tables, but this isn't strictly necessary to perform a lookup with a variable table name. For example, we could just use the IF function to swap tables like this:

=VLOOKUP(E5,IF(F5="a",vendor_a,vendor_b),2,0)

Or, without named ranges:

=VLOOKUP(E5,IF(F5="a",$B$5:$C$8,$B$11:$C$14),2,0)

Here, the IF function simply checks the value in column F and returns one range if the letter is "a", and another if not. This approach won't scale as well (the formula will become progressively more complex as we add more vendor tables) but it works fine. It also nicely demonstrates how one function can be nested inside another to deliver a range instead of a single value.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.