Excel formula: Dynamic lookup table with INDIRECT

Generic formula

=VLOOKUP(A1,INDIRECT("text"),column)

Explanation

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:

=VLOOKUP(F5,INDIRECT(E5),2,0)

Background

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.

How this formula works

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" refers to B4:C6, and "table2" refers to B9:C11*.

INDIRECT picks up the text in E5 ("table1") and resolves it the named range table1, which resolves to B9:C11, which is returned to VLOOKUP.

VLOOKUP performs the lookup and returns 12 for the color "blue" in table1.

* 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.

Author

Dave Bruns

Related formulas

3D SUMIF for multiple worksheets

To conditionally sum identical ranges that exist in separate worksheets, all in one formula, you can do so with the SUMIF function + INDIRECT, wrapped in SUMPRODUCT. In the example, the formula looks like this: = SUMPRODUCT ( SUMIF ( INDIRECT (...

Lookup with variable sheet name

To create a lookup with a variable sheet name, you can use the VLOOKUP function together with the INDIRECT function. In the example shown, the formula in C5 is: = VLOOKUP ( $B5 , INDIRECT ( "'" & C$4 & "'!" & "B5...

Two-way lookup with VLOOKUP

Preface Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create...

Calculate grades with VLOOKUP

To assign a grade based on a score, you can use the VLOOKUP function configured to perform an approximate match. In the example shown, "key" is the named range B5:C9, the formula in F5. copied down, is: = VLOOKUP ( E5 , key , 2 , TRUE ) where "key"...

Get employee information with VLOOKUP

If you want to retrieve employee information from a table, and the table contains a unique id to the left of the information you want to retrieve, you can easily do so with the VLOOKUP function. In the example shown, the VLOOKUP formula looks like...

Merge tables with VLOOKUP

To merge tables, you can use the VLOOKUP function to lookup and retrieve data from one table to the other. To use VLOOKUP this way, both tables must share a common id or key. This article explains how join tables using VLOOKUP and a calculated...

VLOOKUP without #N/A error

To hide the #N/A error that VLOOKUP throws when it can't find a value, you can use the IFERROR function to catch the error and return any value you like. How the formula works When VLOOKUP can't find a value in a lookup table, it returns the #N/A...

Related functions

Excel INDIRECT Function

The Excel INDIRECT function returns a valid reference from a given text string. Use INDIRECT when you need to convert a reference assembled as text into a proper reference.

Excel VLOOKUP Function

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the...

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.