Home › Practice questions › XLOOKUP › Question 5

Excel XLOOKUP Practice Question: Using XLOOKUP, find the latest (last) amount paid by the customer typed in D2…

By Rinkesh Shah, founder of Cellgrids.

The question

Using XLOOKUP, find the latest (last) amount paid by the customer typed in D2 and put it in E2. Search from the bottom.

The data

	A	B	D	E
1	Customer	Amount	Customer	Latest
2	Asha	500	Asha	?
3	Ben	300
4	Asha	700
5	Chitra	200
6	Ben	450
7	Asha	900

The yellow cell is where your answer goes.

Show answer

Answer in E2: 900

How it works

Explanation: The sixth argument, search mode, can be -1 to search from the last row upwards. This returns the last match instead of the first.
Syntax: =XLOOKUP(D2,A2:A7,B2:B7,"",0,-1)
Asha appears three times (500, 700, 900). Searching from the bottom finds the last one first: 900.

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.