Lookup & Reference
VLOOKUP with Concatenated Composite Key: Delimiter-Safe Multi-Criteria Lookup
Perform fast O(1) VLOOKUP multi-column lookups in legacy and modern workbooks by building a delimiter-protected leftmost composite key A2 = B2 & "|" & C2.
Never concatenate B2 & C2 without a "|" delimiter—or ("AB", "CD") collides with ("A", "BCD")!
Because classic VLOOKUP searches ONLY the leftmost column of table_array and accepts a single lookup_value, multi-criteria VLOOKUP requires a leftmost composite key column A2 = B2 & "|" & C2! Beginners often write =B2 & C2 without a separator—creating silent false collisions whenever Warehouse "N1" + Bin "20" ("N120") matches Warehouse "N12" + Bin "0" ("N120")!
Inserting a structural delimiter like "|" that never appears in SKU, warehouse, or size codes guarantees collision-free composite keys ("WH1|M" vs "WH1|L"), allowing VLOOKUP(F2 & "|" & F3, A2:D5, 4, FALSE) to retrieve exact bin stock in legacy Excel 2010–2019, Excel 365, and Arte Sheets alike!
| Query Criteria (Warehouse F2, Size F3) | Delimited Lookup Key F2 & "|" & F3 | Matched Stock Qty (Col 4) | Why "|" Delimiter Matters |
|---|---|---|---|
| Warehouse "WH1", Size "L" | "WH1|L" | 185 units | Distinguishes WH1|L from WH2|L |
| Warehouse "WH2", Size "M" | "WH2|M" | 310 units | Works in every Excel version |
| Warehouse "WH3", Size "XL" (Absent) | "WH3|XL" | "Combination Not Stocked" | IFNA traps missing composite key |
Delimiter-Safe Composite-Key VLOOKUP Formula
Concatenates F2 & "|" & F3 and looks up column 4 (On_Hand_Qty) across A2:D5, returning "Combination Not Stocked" when absent.
=IFNA(VLOOKUP(F2 & "|" & F3, A2:D5, 4, FALSE), "Combination Not Stocked")Audit Duplicate Composite Keys in Table Before VLOOKUP
Checks whether target composite key F2 & "|" & F3 appears more than once in key column A2:A5 (since VLOOKUP returns only the first match).
=COUNTIF(A2:A5, F2 & "|" & F3)Try the example
Use the original values or change them to test your understanding.