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.

=SUMIFS(C2:C100, A2:A100, G1, B2:B100, G2)→4,200

When each pair occurs once and the result is a number, SUMIFS is a simpler alternative.

=XLOOKUP(1, (A2:A100=G1)*(B2:B100=G2), C2:C100)→4,200

Multiplying the two tests avoids building a joined key.

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.

How do I add a third criterion?

Add another test: =XLOOKUP(1, (A2:A100=G1)*(B2:B100=G2)*(D2:D100=G3), C2:C100).

What if no row matches both criteria?

XLOOKUP returns #N/A. Add a fourth argument to show your own text: =XLOOKUP(1, (A2:A100=G1)*(B2:B100=G2), C2:C100, "Not found").

Can I look up two criteria with VLOOKUP?

Only with a helper column that joins the two fields, for example =A2&"|"&B2, placed to the left of the value you want to return.

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