Home › Practice questions › XLOOKUP › Question 4

Excel XLOOKUP Practice Question: The code in D2 is not in the list. Make the formula show Not listed instead of…

By Rinkesh Shah, founder of Cellgrids.

The question

The code in D2 is not in the list. Make the formula show Not listed instead of an error and put the result in E2.

The data

	A	B	D	E
1	Code	Item	Code	Item
2	P-201	Lamp	P-999	?
3	P-202	Fan
4	P-203	Heater
5	P-204	Cooler

The yellow cell is where your answer goes.

Show answer

Answer in E2: Not listed

How it works

Explanation: The fourth argument of XLOOKUP is the text to show when nothing matches. No separate IFERROR is needed.
Syntax: =XLOOKUP(D2,A2:A5,B2:B5,"Not listed")
P-999 is not in the list, so XLOOKUP returns the text from its fourth argument: Not listed.

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.