Excel 365 + Google Sheets · XLOOKUP (2 criteria)
XLOOKUP with two conditions
Multiply the conditions to get 1 where both match, then look for that 1.
The formula
=XLOOKUP(1, (A2:A5="Laura") * (B2:B5="Nov"), C2:C5)What each part means
1Look for the row where...(A2:A5="Laura") * (B2:B5="Nov")...both conditions are trueC2:C5Return this
Example
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Month | Sales |
| 2 | Laura | Oct | 1,100 |
| 3 | João | Nov | 980 |
| 4 | Laura | Nov | 1,250 |
| 5 | João | Oct | 870 |
| 6 | Laura + Nov |
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Month | Sales |
| 2 | Laura | Oct | 1,100 |
| 3 | João | Nov | 980 |
| 4 | Laura | Nov | 1,250 |
| 5 | João | Oct | 870 |
| 6 | Laura + Nov | 1,250 |
Step by step
- Click cell C6.
- Type the formula:
=XLOOKUP(1, (A2:A5="Laura") * (B2:B5="Nov"), C2:C5) - Press Enter.
- Drag the small square in the bottom-right corner of the cell down to copy it to the other rows.
Where it works
- Excel 365 and Excel 2021 or newer
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English XLOOKUP (2 criteria)
- Español BUSCARX (2 criterios)
- Português PROCX (2 critérios)