Multiple chained VLOOKUPs in Excel

by

This tutorial shows how to  work  Multiple chained VLOOKUPs in Excel using the example below;

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.

Worked Example:   How to calculate two-way lookup VLOOKUP in Excel Table

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.

Worked Example:   Excel VLOOKUP and HLOOKUP functions Examples

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.

Worked Example:   VLOOKUP by date in Excel

Leave a Reply

Your email address will not be published.