How to Find the Last Occurrence of a Value in Excel
Last Occurrence Lookup is a Excel function that returns the row number of the last occurrence of a specified value in a column.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Struggling to find the last match of a value in a column? This formula will help you pinpoint the most recent occurrence efficiently.
The Formula
"Find the last (most recent) occurrence of a value in a column instead of the first, when multiple matches exist"
=LOOKUP(2,1/(A:A=E1),ROW(A:A))This formula returns the row number of the last occurrence of a specified value in a column.
Step-by-Step Breakdown
- The formula uses LOOKUP to search for a large number (2) in an array created by dividing 1 by the condition (A:A=E1).
- The division results in an array of 1s and errors, where 1 corresponds to matches of the value in E1.
- LOOKUP finds the last 1 in this array, which corresponds to the last occurrence of the value.
- ROW(A:A) returns the row numbers of the entire column, allowing LOOKUP to return the correct row number.
Edge Cases & Warnings
- The value does not exist in the column, returning an error.
- Multiple identical values are present, and the formula correctly identifies the last one.
- The search column contains blank cells, which may affect the results if not handled properly.
Examples
"Value 'Apple' in column A with last occurrence in A10"
10"Value 'Banana' in column A with last occurrence in A5"
5Frequently Asked Questions
Can this formula handle empty cells?
Yes, but ensure your data does not have unexpected blanks that could affect results.
What if the value appears only once?
The formula will still return the row number of that single occurrence.
Is this formula compatible with Excel and Google Sheets?
Yes, the formula works in both Excel and Google Sheets.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.