How to Return the Last Matching Value in Excel Without Errors
Question details
The user wants to retrieve the last matching value in a dataset using a lookup formula while preventing errors when the searched item does not exist.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching a spreadsheet dataset for the most recent or last occurrence of an item and displaying a custom message instead of a default error if the item is missing.
- Observed behavior
- Standard lookup formulas like VLOOKUP return the first match instead of the last, and produce a #N/A error if the exact item is missing from the range.
Ensure your dataset is properly organized and clearly identify the specific column where your search criteria and return values are stored. Formatting your data as a Table can make writing and managing these advanced formulas significantly easier.
Use the LOOKUP Formula to Find the Last Match
The traditional LOOKUP function can be manipulated with an array to scan a column and reliably return the last corresponding value for a matched item.
By dividing 1 by a condition array, you create a series of 1s (True) and #DIV/0! errors (False). Searching for '2' forces the formula to ignore the errors and lock onto the last numeric '1', effectively yielding the last match in the dataset.
Click on the empty cell where you want the lookup result to appear.
Type the formula =LOOKUP(2,1/(LookupRange=SearchCriteria),ReturnRange). For example, if your item is in E2, type =LOOKUP(2,1/(CountLogTbl[Item]=E2),CountLogTbl[Count]).
Press Enter. The cell will now display the value associated with the very last instance of your searched item.
Use IF and COUNTIF to Prevent Missing Item Errors
Before executing any lookup, use a COUNTIF statement to check if the item actually exists in your range, preventing #N/A errors from appearing.
Perform Advanced Data Lookups for Free in WPS Office
WPS Spreadsheet fully supports all standard Excel formulas, including complex LOOKUP, XLOOKUP, and IF statements. You can seamlessly execute data queries, manage large datasets, and handle errors with high performance.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook.
- 2. Select the result cell: Click on the cell where you want to perform the reverse lookup.
- 3. Input the LOOKUP function: Type the =LOOKUP(2,1/(Range=Criteria),ResultRange) formula directly into the formula bar.
- 4. Press Enter: Hit Enter to instantly retrieve the last matching value with complete cross-platform compatibility.
Frequently Asked Questions
Why does my VLOOKUP formula return the first match instead of the last?
Standard VLOOKUP functions are designed to scan data sequentially from top to bottom. They automatically stop and return a value at the very first exact match they find. To get the last match, you must use alternative functions like LOOKUP or XLOOKUP configured for bottom-to-top searches.
Can I use XLOOKUP to find the last matching value?
Yes. In modern spreadsheet software, you can use the XLOOKUP function and set its [search_mode] argument to -1. This instructs the formula to search from last to first, easily returning the final matching value in the dataset.
Is there a simpler way to hide the #N/A error when an item is missing?
Instead of using the COUNTIF method, you can wrap your entire lookup formula in the IFERROR function, like this: =IFERROR(LOOKUP(...), "Not Found"). This will catch any error generated by the formula and replace it with your specified custom text.
What does the '2, 1/()' part of the LOOKUP formula actually do?
The expression '1/(Range=Criteria)' generates an array of 1s (where the condition is met) and #DIV/0! errors (where it is not). By instructing LOOKUP to search for the number '2' in an array that only contains 1s and errors, the function ignores the errors and matches the last valid numeric value (the final 1), which aligns with your last matching item.
