Exceljet

Quick, clean, and to the point

Multiple chained VLOOKUPs

Excel formula: Multiple chained VLOOKUPs
Generic formula 
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

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.