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. 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: 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: And, since “a” and “b” are already in column F, we can pick up that value directly: 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: 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: Carrying these changes into the final formula, we have: 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: Or, without named ranges: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
