Return the Last Entry for the Latest Date in Excel
Question details
The user needs to extract the final matching record in a dataset when multiple entries share the most recent date.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for the most recent data entry in a spreadsheet where multiple entries might have the exact same latest date.
- Observed behavior
- Combining VLOOKUP and MAX formulas returns the first matching record for the latest date instead of the very last entry in the column.
Ensure your data is sorted chronologically by date and identify the exact starting and ending row numbers of your dataset before modifying your formulas.
Limit Lookup Ranges to Exact Data Rows
Replace full-column references with exact range limits to force the formula to process the specific data block, making it easier to accurately identify the final row.
When using functions like VLOOKUP or INDEX/MATCH combined with MAX, full-column references (like A:A) can cause logical conflicts or performance issues, often resulting in the formula grabbing the first matching record rather than the last.
By restricting the referenced area to the exact dataset range, you ensure your formulas or add-in scripts correctly evaluate the bottom-most boundary of your records.
Select the cell containing your current lookup formula that is returning the incorrect first entry.
Click into the formula bar and look for any generic column ranges, such as A:A, C:C, or D:D.
Change the full-column references to your specific data boundaries. For example, replace C:C with $C$5:$C$104.
Ensure all other ranges in your formula (like the return array) use the exact same starting and ending row numbers.
Press Enter to execute the updated formula and successfully retrieve the bottom-most entry for the latest date.

Use a Bottom-Up LOOKUP Formula
Utilize the classic LOOKUP division-by-zero trick to force the spreadsheet to search from the bottom of the data up to the top.
Easily Manage Complex Formulas in WPS Spreadsheet
WPS Spreadsheet provides a robust, fast, and highly compatible environment for handling advanced lookup array formulas, large datasets, and exact range definitions.
- 1. Download WPS Office: Download and install the free WPS Office suite from the official website.
- 2. Open your workbook: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx or .xls).
- 3. Input the exact range formula: Select your target cell and input your precise lookup ranges instead of full-column references.
- 4. Calculate the result: Press Enter to instantly calculate and retrieve the last entry for your desired date.

Frequently Asked Questions
Why does VLOOKUP always return the first match?
VLOOKUP is designed by default to scan a column from top to bottom and automatically stops at the first exact match it encounters. To find the last match in a list with duplicate lookup values, you must use alternative approaches like LOOKUP, XLOOKUP, or an INDEX/MATCH array formula.
Can I use XLOOKUP to find the last entry instead?
Yes. If your spreadsheet software supports XLOOKUP, you can use it to easily solve this issue. Simply set the 'search_mode' argument (the 6th parameter) to -1. This instructs the function to search from the last item up to the first, perfectly returning the bottom-most record.
Does referencing full columns like A:A slow down my spreadsheet?
Yes. Referencing entire columns forces the spreadsheet engine to calculate over one million rows per formula, which can significantly slow down performance and increase file lag. It is best practice to always use exact data ranges like $A$2:$A$500.




