Formula guide · Excel and Google Sheets · Updated May 13, 2026
Lookup with Multiple Criteria
Find a row by matching two or more conditions in Excel or Google Sheets.
Quick Answer
Use XLOOKUP with Boolean conditions multiplied together when every criterion must match.
Copyable Formula
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),D2:D100,"Not found")Syntax
=XLOOKUP(1,(criteria_range1=criteria1)*(criteria_range2=criteria2),return_range)Excel and Google Sheets
Worked Example
| A | B | D | F | G |
|---|---|---|---|---|
| Client | Month | Amount | Client | Month |
| Acme | May | 300 | Acme | May |
| Acme | June | 420 |
Result: Returns the amount for the row where both Client and Month match.
Steps
- Create one condition for each column that must match.
- Multiply the conditions together for AND logic.
- Use 1 as the lookup value because TRUE times TRUE becomes 1.
- Choose the return range from the row you want back.
Common Mistakes
- Using only one criterion when duplicate keys exist.
- Mixing ranges with different row counts.
- Forgetting that older Excel versions may require array-entry alternatives.
Excel vs Google Sheets Notes
The core idea works in both Excel and Google Sheets, but separators, function availability, and array behavior can vary by account, locale, and version.
Editorial check: This guide was last updated May 13, 2026. Formula behavior can vary by Excel version, Google Sheets rollout, and spreadsheet locale.