Excel formula: Multiple chained VLOOKUPs

Generic formula

=IFERROR(VLOOKUP 1,IFERROR(VLOOKUP 2,VLOOKUP 3))

Explanation

If you need to perform multiple lookups sequentially, based on whether the earlier lookups succeed or not, you can chain one or more VLOOKUPs together with IFERROR.

In the example shown, the formula in L5 is:

=IFERROR(VLOOKUP(K5,B5:C7,2,0),IFERROR(VLOOKUP(K5,E5:F7,2,0),VLOOKUP(K5,H5:I7,2,0)))

How this formula works

The IFERROR function is designed to trap errors and perform an alternate action when an error is detected. The VLOOKUP function will throw an #N/A error when a value isn't found.

By nesting multiple VLOOKUPs inside the IFERROR function, the formula allows for sequential lookups. If the first VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP. If the second VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP, and so on.

Author

Dave Bruns

Related formulas

VLOOKUP with 2 lookup tables

To use VLOOKUP with a variable table array, you can use the IF function inside VLOOKUP to control which table is used. In the example shown the formula in cell E4 is: = VLOOKUP ( D5 , IF ( C4 < 2 , table1 , table2 ), 2 , TRUE ) This formula uses...

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 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 IFERROR Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.

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.