Home › Practice questions › XLOOKUP › Question 11

Excel XLOOKUP Practice Question: Customers are in A2:B6. IDs to look up are in D2:D5. Show the city in F2:F5,…

By Rinkesh Shah, founder of Cellgrids.

The question

Customers are in A2:B6. IDs to look up are in D2:D5. Show the city in F2:F5, or Unknown if the ID is not in the list, using XLOOKUP.

The data

	A	B	D	F
1	Customer ID	City	Find ID	City found
2	C-1001	Mumbai	C-1004	?
3	C-1002	Pune	C-1009	?
4	C-1003	Delhi	C-1001	?
5	C-1004	Chennai	C-1005	?
6	C-1005	Kolkata

The yellow cells are where your answer goes.

Show answer

Expected results:

F2 = Chennai
F3 = Unknown
F4 = Mumbai
F5 = Kolkata

How it works

Explanation: XLOOKUP takes the lookup value, the column to search, the column to return and an optional message for when nothing is found, so no IFERROR is needed.
Syntax: =XLOOKUP(D2,$A$2:$A$6,$B$2:$B$6,"Unknown")
C-1009 is not in the list, so it shows Unknown.

Try it yourself in the live sheet

Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.

Try this question in the live sheet →

XLOOKUP syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

The modern replacement for VLOOKUP — searches any column and returns from any other, no column counting needed. XLOOKUP is used to pull matching data from one part of a workbook into another. Full XLOOKUP guide.