Dynamic lookup table with INDIRECT in Excel

by

This tutorial shows how to calculate Dynamic lookup table with INDIRECT in Excel using the example below;

To allow a dynamic lookup table, you can use the INDIRECT function with named ranges inside of VLOOKUP.

Formula

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

Explanation

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.

Worked Example:   Excel Data validation specific characters only

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.

Worked Example:   How to use Excel INDIRECT Function

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.

Worked Example:   Win loss points calculation in Excel

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.

Leave a Reply

Your email address will not be published.