Excel 365 + Google Sheets · XLOOKUP
Stop using VLOOKUP
Find any value in another table, left or right, with a clean message when it's missing.
The formula
=XLOOKUP(B2, Clients!A:A, Clients!B:B, "Not found")What each part means
B2What you're looking forClients!A:AWhere to lookClients!B:BWhat to bring back"Not found"What to show if it's missing
Example
| A | B | C | |
|---|---|---|---|
| 1 | Order | Client ID | Client |
| 2 | #1041 | C-07 | |
| 3 | #1042 | C-12 | |
| 4 | #1043 | C-03 | |
| 5 | #1044 | C-99 | |
| 6 | #1045 | C-07 |
| A | B | C | |
|---|---|---|---|
| 1 | Order | Client ID | Client |
| 2 | #1041 | C-07 | Laura Gómez |
| 3 | #1042 | C-12 | João Silva |
| 4 | #1043 | C-03 | Priya Shah |
| 5 | #1044 | C-99 | Not found |
| 6 | #1045 | C-07 | Laura Gómez |
Step by step
- Click cell C2.
- Type the formula:
=XLOOKUP(B2, Clients!A:A, Clients!B:B, "Not found") - 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)