Table of Contents
To find the last instance of a matching value in Excel, you can use the LOOKUP function. This function searches for a value in a range and returns the last matching value. Simply specify the search criteria and the range to search in. Alternatively, you can use the COUNTIF function to count the number of occurrences of the value, and then use the INDEX and MATCH functions to return the last occurrence. Another option is to use the MAX function in combination with the IF function to find the maximum value that meets the search criteria. These methods can help you efficiently locate the last instance of a matching value in your Excel spreadsheet.
Excel: Find Last Instance of Matching Value
You can use the following formula to return the last instance of a matching value in Excel:
=INDEX(B2:B11,SUMPRODUCT(MAX(ROW(A2:A11)*(F1=A2:A11))-1))
This particular formula will look up the value in cell F1 in the range A2:A11 and then return the value from the range B2:B11 that corresponds to the last instance of the matching value.
The following example shows how to use this formula in practice.
Example: Find Last Instance of Matching Value in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to return the value in the points column that corresponds to the last instance of “Mavs” in the team column.
We can type the following formula into cell F2 to do so:
=INDEX(B2:B11,SUMPRODUCT(MAX(ROW(A2:A11)*(F1=A2:A11))-1))
The following screenshot shows how to use this formula in practice:
The formula returns a value of 15, which is the value in the points column that corresponds to the last instance of “Mavs” in the team column.
If we would like to return the value in the assists column instead, we could change the cell range B1:B11 to C1:C11:
=INDEX(C2:C11,SUMPRODUCT(MAX(ROW(A2:A11)*(F1=A2:A11))-1))
The following screenshot shows how to use this formula in practice:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I find the last instance of a matching value in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-find-the-last-instance-of-a-matching-value-in-excel/
stats writer. "How can I find the last instance of a matching value in Excel?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-find-the-last-instance-of-a-matching-value-in-excel/.
stats writer. "How can I find the last instance of a matching value in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-find-the-last-instance-of-a-matching-value-in-excel/.
stats writer (2024) 'How can I find the last instance of a matching value in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-find-the-last-instance-of-a-matching-value-in-excel/.
[1] stats writer, "How can I find the last instance of a matching value in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I find the last instance of a matching value in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.