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.
Your first month for $9.50 $19
50% off your first month when you join today.
Then $19/month. Cancel any time, and get a full refund within 14 days if it isn't for you.
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.