XLOOKUP With Multiple Criteria

A basic XLOOKUP matches on a single value, but many real lookups need to match on two or more conditions at once - for example, finding a price that depends on both a product and a region. XLOOKUP handles this by combining an array expression directly inside lookup_array.

This page covers that technique using an order table where both Customer Name and City together identify the correct row.

In this order table, Customer Name alone isn't guaranteed to be unique across a larger dataset, so matching also needs to check City:

Instead of referencing a single column, lookup_array is built as an array expression that concatenates two columns together, matched against a similarly concatenated lookup value:

B2:B4&C2:C4 creates a temporary array where each row combines its Customer Name and City into one string, such as "Rohan GuptaChennai". As long as the search value F2&F3 is built the same way, XLOOKUP can match the combined string exactly, effectively matching on both columns at once.

Concatenating without a separator can occasionally create accidental matches between different value combinations. Adding a separator character reduces that risk:

Turbo Excel VLookup supports matching on multiple key columns directly through its Key Column Pairs feature, building a composite match from several columns without needing an array formula or a manually built separator string.

Just like VLOOKUP, XLOOKUP will fail to match values that look identical but are stored as different data types - text versus number is the most common culprit.

If a lookup that should clearly match is instead falling through to if_not_found, converting the lookup value with VALUE() or TEXT() to match the source column's format usually resolves it.

A short comment or adjacent label explaining what an XLOOKUP formula is matching and why makes a shared workbook far easier to maintain, especially once several lookups start feeding into each other across different sheets.

XLOOKUP's optional fifth argument, match_mode, defaults to an exact match, but it also supports approximate matches - either the next smaller or next larger value when no exact match exists - by passing -1 or 1 instead of the default 0.

This is useful for scenarios like matching a score against grade thresholds, where an exact match won't always exist but the closest applicable bracket still needs to be found.