CURRENT EXCEL
Use XLOOKUP with two tests
Suppose column A contains account IDs, column B contains months, column C contains the value to return, and E2 and F2 contain the two requested criteria.
Find the row where both criteria are true
=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"Not found")Each comparison produces TRUE or FALSE. Multiplication turns the row where both are true into 1, which XLOOKUP searches for.
COMPATIBLE
Use INDEX and MATCH for older Excel
Exact two-criteria INDEX and MATCH
=INDEX(C2:C100,MATCH(1,(A2:A100=E2)*(B2:B100=F2),0))Version behavior differs. Older perpetual Excel releases may require confirming this as an array formula, while current Microsoft 365 evaluates it directly.
BOUNDARY
Check the data model before trusting the result
- • Keep all three ranges the same size and avoid full-column array calculations.
- • Confirm that text and number criteria use the same underlying data type.
- • Both formulas return the first match; they do not warn about duplicate keys.
- • If duplicates are valid, use FILTER or an aggregation instead of a first-match lookup.
SOURCE