Lookup & Reference
Nested VLOOKUP & Chained XLOOKUP: Two-Hop Crosswalk Mapping Formula
Translate legacy ERP or vendor codes through an intermediate crosswalk bridge table into master pricing using IFNA(VLOOKUP(VLOOKUP(G2, A2:B5, 2, FALSE), D2:E5, 2, FALSE), "Unmapped Code") or chained XLOOKUP.
Bridging legacy SKU codes to master catalog prices via a two-hop crosswalk lookup!
After an ERP migration or company acquisition, historical invoices still carry Legacy_Code values ("LEG-10", "LEG-20") while current pricing lives in a Master_SKU table ("MST-401", "MST-402")! Instead of adding redundant helper columns to every invoice tab, nest an inner crosswalk lookup directly inside the outer pricing lookup!
The inner VLOOKUP(G2, A2:B5, 2, FALSE) translates "LEG-20" -> "MST-402", and the outer VLOOKUP(..., D2:E5, 2, FALSE) resolves "MST-402" -> $290, while a single outer IFNA catches #N/A whether the break occurred in the crosswalk table OR the master price table!
| Invoice Legacy Code G2 | Hop 1: Crosswalk Master SKU | Hop 2: Master Unit Price | Broken-Chain Handling |
|---|---|---|---|
| G2 = "LEG-20" | "MST-402" | $290 | Clean 2-hop resolution |
| G2 = "LEG-30" | "MST-403" | $410 | Matches both VLOOKUP & XLOOKUP |
| G2 = "LEG-99" (Missing in Hop 1) | #N/A intercepted | "Unmapped Legacy or Master SKU" | Single outer IFNA / XLOOKUP fallback |
Two-Hop Nested VLOOKUP Wrapped in IFNA(VLOOKUP(VLOOKUP(...)), Fallback)
Resolves Legacy_Code G2 in crosswalk A2:B5, then looks up the resulting Master_SKU in price list D2:E5.
=IFNA(VLOOKUP(VLOOKUP(G2, A2:B5, 2, FALSE), D2:E5, 2, FALSE), "Unmapped Legacy or Master SKU")Equivalent Chained XLOOKUP(XLOOKUP(...)) Crosswalk Formula
Chains two exact-mode XLOOKUP calls without hard-coded column index numbers.
=XLOOKUP(XLOOKUP(G2, A2:A5, B2:B5, "__MISSING__"), D2:D5, E2:E5, "Unmapped Legacy or Master SKU")Try the example
Use the original values or change them to test your understanding.