Lookup & Reference
Lookup Across Selectable Rate Tables Without Volatile INDIRECT (IFS & VLOOKUP)
Route SKU price lookups dynamically between Standard and Partner price books based on a customer tier cell using non-volatile IFS and VLOOKUP.
Replace fragile INDIRECT(tableName) strings with explicit IFS routing
Many legacy templates switch price books via VLOOKUP(sku, INDIRECT(tierCell), 2, FALSE). Because INDIRECT is volatile, it recalculates on every workbook edit and breaks silently whenever a sheet or range is renamed.
Routing explicit VLOOKUP calls inside IFS(tier = "Standard", VLOOKUP(sku, A2:B4, 2, FALSE), tier = "Partner", VLOOKUP(sku, D2:E4, 2, FALSE), TRUE, "Unknown Tier") wrapped in IFNA keeps dependency tracking 100% non-volatile and self-documenting.
| SKU | Standard Book (A2:B4) | Partner Book (D2:E4) | Partner Savings |
|---|---|---|---|
| SKU-100 | $120 | $96 | $24 (20%) |
| SKU-200 | $180 | $144 | $36 (20%) |
| SKU-300 | $250 | $200 | $50 (20%) |
Non-Volatile Multi-Book VLOOKUP Router
Routes SKU B6 to Standard range A2:B4 or Partner range D2:E4 according to tier B7.
=IFNA(IFS(B7 = "Standard", VLOOKUP(B6, A2:B4, 2, FALSE), B7 = "Partner", VLOOKUP(B6, D2:E4, 2, FALSE), TRUE, "Unknown Rate Book"), "SKU Not Found")Extended Line Total for Selected Price Book and Quantity B8
Multiplies the routed unit price by order quantity B8 when valid.
=LET(unitPrice, IFNA(IFS(B7 = "Standard", VLOOKUP(B6, A2:B4, 2, FALSE), B7 = "Partner", VLOOKUP(B6, D2:E4, 2, FALSE), TRUE, -1), -1), IF(unitPrice < 0, "Check SKU or Book", unitPrice * B8))Try the example
Use the original values or change them to test your understanding.