How to Find the Last Match Using XLOOKUP?
Find Last Match with XLOOKUP is a Excel function that retrieves the last occurrence of a specified value from a range by searching in reverse order.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Discover how to efficiently retrieve the most recent entry of a value in a dataset using XLOOKUP's reverse search feature.
The Formula
"Find the last occurrence of a value (most recent entry) using XLOOKUP with reverse search mode (-1)"
=XLOOKUP(value, range, return_range, , -1)This formula retrieves the last occurrence of a specified value from a range by searching in reverse order.
Step-by-Step Breakdown
- Specify the value you want to find.
- Define the range where the value is searched.
- Indicate the return range from which to pull the corresponding result.
- Use -1 as the fourth argument to enable reverse search mode.
Edge Cases & Warnings
- The value does not exist in the range, resulting in an error.
- Multiple identical values are present, and the last one is returned correctly.
- The search range is empty, leading to an error.
- The return range is shorter than the search range, causing a mismatch.
Examples
"XLOOKUP("apple", A1:A5, B1:B5, , -1) where A1:A5 = {"banana", "apple", "orange", "apple", "grape"} and B1:B5 = {1, 2, 3, 4, 5}"
4"XLOOKUP("orange", A1:A5, B1:B5, , -1) where A1:A5 = {"banana", "apple", "orange", "apple", "grape"} and B1:B5 = {1, 2, 3, 4, 5}"
3Frequently Asked Questions
What happens if the value is not found?
The formula will return an error indicating that the value is not found.
Can I use XLOOKUP with non-contiguous ranges?
No, XLOOKUP requires contiguous ranges for both the lookup and return ranges.
Is XLOOKUP available in all versions of Excel?
XLOOKUP is available in Excel 365 and Excel 2021 and later versions.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.