Lookup & Reference
Chained / Nested Two-Step Lookup Across Mapping Tables (XLOOKUP)
Bridge normalized relational tables (such as Employee -> Job Band -> Bonus Target %) in a single formula by nesting one XLOOKUP or VLOOKUP as the lookup_value of a second.
Look up an intermediate key, then its mapped value
The first table maps an employee ID to a band code; the second maps that code to a hypothetical bonus rate. The examples return a fraction such as 0.25, which can be displayed as 25%. They do not calculate an actual payment or define an employment policy.
A missing first lookup must remain an error until IFNA handles it. Returning a text marker such as MissingEmp inside the first lookup is unsafe: that marker might itself be a real key in the second table. Both missing stages here return Unmapped; use separate helper lookups if you need to distinguish which relationship is absent.
Keep keys unique and nonempty. Exact lookup is case-insensitive and the first duplicate wins. These examples require nonempty band cells and three numeric rates between zero and one. The VLOOKUP alternative escapes literal wildcard characters at both stages to match XLOOKUP exact matching.
| Lookup Stage for G2 = "Emp-102" | Table Searched | Intermediate / Final Output |
|---|---|---|
| 1. Inner XLOOKUP(G2, A2:A4, B2:B4) | Employee Roster (A2:B4) | Returns "Band-L6" |
| 2. Outer XLOOKUP("Band-L6", D2:D4, E2:E4) | Compensation Policy (D2:E4) | Returns 0.25 (25% Bonus) |
| Missing employee ID | First lookup returns #N/A | Outer IFNA returns Unmapped without looking up a text marker |
Chained Two-Hop Relational XLOOKUP
Omit the inner if_not_found argument. A missing employee or band propagates #N/A to the outer IFNA. No text sentinel becomes a lookup key.
=IF(OR(NOT(ISTEXT(G2)),COUNTBLANK(G2)>0),"Enter employee ID",IF(OR(COUNTBLANK(B2:B4)>0,COUNTBLANK(D2:D4)>0,COUNT(E2:E4)<>3),"Invalid mapping",IF(OR(MIN(E2:E4)<0,MAX(E2:E4)>1),"Invalid mapping",IFNA(XLOOKUP(XLOOKUP(G2,A2:A4,B2:B4),D2:D4,E2:E4),"Unmapped"))))Legacy Nested VLOOKUP Equivalent
Use FALSE for both exact VLOOKUP calls and escape literal wildcard characters in both keys. IFNA handles missing mappings, while other spreadsheet errors remain visible.
=IF(OR(NOT(ISTEXT(G2)),COUNTBLANK(G2)>0),"Enter employee ID",IF(OR(COUNTBLANK(B2:B4)>0,COUNTBLANK(D2:D4)>0,COUNT(E2:E4)<>3),"Invalid mapping",IF(OR(MIN(E2:E4)<0,MAX(E2:E4)>1),"Invalid mapping",IFNA(VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?"),A2:B4,2,FALSE),"~","~~"),"*","~*"),"?","~?"),D2:E4,2,FALSE),"Unmapped"))))Try the example
Use the original values or change them to test your understanding.