Lookup & Reference

Excel formula for Look up with two criteria

Find a value that matches two columns at once by combining keys.

Copy this formula

=XLOOKUP(A2&"|"&B2, Region&"|"&Month, Sales)

How the formula works

Concatenate the two lookup values and the two lookup columns with a separator, so XLOOKUP matches the combined key. The separator (here "|") avoids accidental collisions between columns.

Examples

=XLOOKUP(G1&"|"&G2, A2:A100&"|"&B2:B100, C2:C100)→4,200

Sales for the matching region + month.

Other ways to do this

INDEX/MATCH (older Excel)

=INDEX(C2:C100, MATCH(G1&"|"&G2, A2:A100&"|"&B2:B100, 0))

Enter as an array in pre-365 Excel.

FAQ

Why use a separator?

Without one, "12"&"3" and "1"&"23" both become "123" and could match the wrong row. A separator like "|" keeps keys distinct.

Related formulas

Look up a value with XLOOKUP Two-way lookup (row and column)

Functions used

XLOOKUP INDEX MATCH

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free