Advanced lookups — Excel Course

Combine XLOOKUP, INDEX, MATCH and XMATCH for robust retrieval. Learn it step by step with guided practice on Excelquro.

Understand

Robust lookup design begins with key quality. For two-way retrieval, one position identifies the row and another identifies the column. For multi-condition lookups, combine criteria into a controlled key or use a formula pattern appropriate to the data. Never hide duplicates accidentally.

Worked example

Combining Employee and Month creates a composite lookup key. In a production model, a dedicated composite key column can improve auditability.

=XLOOKUP(H2&H3,A2:A5&B2:B5,C2:C5,"Not Found")

Follow along

1. Check whether the original key is unique.
2. If the key needs two fields, define a composite key.
3. Use exact match and an explicit missing-data result.
4. Test a duplicate record deliberately.
5. For two-way tables, identify row and column positions separately.
6. Document the rule for duplicate and missing keys.

Interactive practice runs in your browser.