Find the latest match with XLOOKUP
PremiumThe price list grows at the bottom each time a price changes. In F2, find the current price of the product in F1, the last match in the list. Search from the bottom with search_mode -1, the sixth argument: XLOOKUP(F1,B2:B8,C2:C8,,0,-1).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Effective from | Product | Price | Product | Coffee | ||
| 2 | Jan 1, 2024 | Coffee | 89 | Current price | |||
| 3 | Jan 1, 2024 | Tea | 45 | ||||
| 4 | Mar 1, 2024 | Coffee | 95 | ||||
| 5 | Apr 1, 2024 | Cocoa | 60 | ||||
| 6 | May 1, 2024 | Tea | 49 | ||||
| 7 | Jul 1, 2024 | Coffee | 99 | ||||
| 8 | Sep 1, 2024 | Tea | 52 | ||||
| 9 |
Premium lesson
Unlock XLOOKUP and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
It comes up in this mock test: Excel test for data analysts
Why this works
XLOOKUP's sixth argument sets the search direction: 1 (the default) goes from the first row down, -1 from the last row up. Coffee has three prices, 89, 95 and 99. Searching from the bottom finds the newest, 99. The two commas leave if_not_found empty, and 0 keeps an exact match.
Common mistake: Using a plain lookup on a list where values repeat. It returns the first, oldest match, not the latest.
The answer
- F2
=XLOOKUP(1F1,2B2:B8,3C2:C8,,50,6-1)
In plain words: Find 1the product in F1 in 2B2:B8 and return 3its price from C2:C8, 5on an exact match, 6searching from the bottom up.