Quick, clean, and to the point

Dynamic lookup table with INDIRECT

Excel formula: Dynamic lookup table with INDIRECT
Generic formula 

To allow a dynamic lookup table, you can use the INDIRECT function with named ranges inside of VLOOKUP. In the example shown the formula in G5 is:



The purpose of this formula is to allow an easy way to switch table ranges inside a lookup function. One way to handle is to create a named range for each table needed, then refer to the named range inside of VLOOKUP. However, if you just try to give VLOOKUP a table array in the form of text (i.e. "table1") the formula will fail. The INDIRECT function is needed to resolve the text to a valid reference.


At the core, this is a standard VLOOKUP formula. The only difference is the use of INDIRECT to return a valid table array.

In the example shown, two named ranges have been created: "table1" (B4:C6), and "table2" (B9:C11)*.

In G5, INDIRECT picks up the text in E5 and resolves it to the named range "table1", which resolves to B4:C6, which is returned to VLOOKUP. VLOOKUP performs the lookup and returns 12 for the color "blue" in table1.

In G6, the process is the same. The text in E6 resolves to "table2", which resolves to B9:C11. With the same lookup value, VLOOKUP returns 24.

* Note: names ranges actually create absolute references like $B$9:$C$11, but I've omitted the absolute reference syntax to make the description easier to read.

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.