Lookup & Reference
Switch Lookup Tables Dynamically with CHOOSE & INDEX / MATCH
Route a two-way INDEX/MATCH lookup across Retail, Wholesale, or Enterprise rate tables dynamically based on a selected tier code using CHOOSE.
Routing pricing lookups across multiple rate matrices without volatile INDIRECT()!
Commercial quoting workbooks often maintain separate rate matrices for Retail (Table 1) and Wholesale (Table 2) customers across identical SKU rows and quantity columns. Many users reach for INDIRECT("Table_" & Tier), which makes every downstream cell volatile and breaks silently whenever a sheet or range is renamed!
Routing the lookup via CHOOSE(MATCH(Tier, {"Retail","Wholesale"}, 0), INDEX(B2:C4, r, c), INDEX(E2:F4, r, c)) stays 100% non-volatile, updates cell references automatically if columns shift, and works identically across every spreadsheet engine!
| Selected Price Book & SKU | Matched Row & Col | Retail Matrix (B2:C4) | Wholesale Matrix (E2:F4) -> Active Quote |
|---|---|---|---|
| Wholesale | SKU-B | Express | Row 2, Col 2 | $145 (Ignored) | $118 (Returned by CHOOSE #2) |
| Retail | SKU-B | Express | Row 2, Col 2 | $145 (Returned by CHOOSE #1) | $118 (Ignored) |
| Unknown Tier "VIP" | MATCH -> #N/A | IFNA Trap | "Unknown Rate Table" |
Dynamic Multi-Table Lookup via CHOOSE + INDEX / MATCH
Selects between Retail rate table B2:C4 (option 1) and Wholesale rate table E2:F4 (option 2) for SKU H2 and shipping speed H3.
=IF(ISNA(MATCH(H1, {"Retail","Wholesale"}, 0)), "Unknown Rate Table or SKU", IFNA(CHOOSE(MATCH(H1, {"Retail","Wholesale"}, 0), INDEX(B2:C4, MATCH(H2, A2:A4, 0), MATCH(H3, B1:C1, 0)), INDEX(E2:F4, MATCH(H2, A2:A4, 0), MATCH(H3, E1:F1, 0))), "Unknown Rate Table or SKU"))Compute Wholesale Savings ($) vs. Retail Matrix for Selected Item
Subtracts the Wholesale quote from the Retail quote for the currently selected SKU (H2) and shipping column (H3).
=IFNA(INDEX(B2:C4, MATCH(H2, A2:A4, 0), MATCH(H3, B1:C1, 0)) - INDEX(E2:F4, MATCH(H2, A2:A4, 0), MATCH(H3, E1:F1, 0)), "Unknown SKU")Try the example
Use the original values or change them to test your understanding.