VLOOKUP with variable table array

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.