Nested IFERROR for multi-table fallback
IntermediateInventory data is not always stored in one clean table. Active items, discontinued products, and special orders often live in separate lists, so a single lookup may not be enough. Nested IFERROR lets you try one lookup, then gracefully fall back to the next source when the first one fails.
In this exercise, you will look up product codes from the query section in A10:B14. Search the code in column A against the main catalog in A2:B7, then the discontinued list in D2:E5, then the special orders list in G2:H4.
How the lookup works
Use VLOOKUP for each table lookup:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Then use IFERROR to move to the next table whenever a lookup returns an error.
Practice
- In B11, build a formula that first checks the main catalog for the code in A11.
- If that code is not found there, make the formula try the discontinued list, then the special orders list.
- If the code does not exist in any of the three tables, return the exact text
Not found. - Copy the finished formula down through B14.
When you are done, B11:B14 should show the matching product names or Not found for the missing code.
Need some help?
Hint 1
Start with a regular VLOOKUP against the main catalog, then wrap that lookup in IFERROR so the formula can keep going when the code is missing there.
Hint 2
The second argument of IFERROR can be another IFERROR around a second VLOOKUP, which lets you chain the discontinued list and the special orders list before returning Not found.
Nested IFERROR for multi-table fallback
IntermediateInventory data is not always stored in one clean table. Active items, discontinued products, and special orders often live in separate lists, so a single lookup may not be enough. Nested IFERROR lets you try one lookup, then gracefully fall back to the next source when the first one fails.
In this exercise, you will look up product codes from the query section in A10:B14. Search the code in column A against the main catalog in A2:B7, then the discontinued list in D2:E5, then the special orders list in G2:H4.
How the lookup works
Use VLOOKUP for each table lookup:
=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)
Then use IFERROR to move to the next table whenever a lookup returns an error.
Practice
- In B11, build a formula that first checks the main catalog for the code in A11.
- If that code is not found there, make the formula try the discontinued list, then the special orders list.
- If the code does not exist in any of the three tables, return the exact text
Not found. - Copy the finished formula down through B14.
When you are done, B11:B14 should show the matching product names or Not found for the missing code.
Need some help?
Hint 1
Start with a regular VLOOKUP against the main catalog, then wrap that lookup in IFERROR so the formula can keep going when the code is missing there.
Hint 2
The second argument of IFERROR can be another IFERROR around a second VLOOKUP, which lets you chain the discontinued list and the special orders list before returning Not found.