How XLOOKUP works
XLOOKUP searches one column and returns from another. It can look left, and it can say what to show when nothing is found.
Task 1The three parts of XLOOKUP3 questions
XLOOKUP does the same job as VLOOKUP with less to go wrong. It is in Google Sheets, in Excel 2021 and in Microsoft 365, but not in older versions of Excel.
- Lookup value
- what to look for
- Lookup range
- the one column to search
- Return range
- the one column the answer comes from
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Account | Town | Credit limit |
| 2 | Hollin School | C101 | Leeds | 1,500.00 |
| 3 | Moss Lane Vets | C102 | York | 2,000.00 |
| 4 | Quay Dental | C103 | Leeds | 750.00 |
| 5 | Tarn Garage | C104 | Hull | 1,200.00 |
=XLOOKUP("C103",B2:B5,C2:C5) searches column B for C103 and returns the town beside it, Leeds. There is no column number to count, and the match is exact unless you say otherwise.
Task 2Looking to the left3 questions
Task 3When nothing is found3 questions