Have you ever struggled to find the last matching value in Google Sheets? Whether you’re trying to track changes in prices or customer purchases, this valuable skill can come in handy. In this tutorial, we’ll explore two efficient solutions using the LOOKUP and XLOOKUP functions that work for both sorted and unsorted data.
LOOKUP Function: Essential Tips for Google Sheets
The LOOKUP function is a powerful tool that can work with both horizontal and vertical datasets, returning the last matching value in a lookup. Here’s an example of its syntax:
LOOKUP(search_key, search_range, result_range)
Let’s say we have the following list showing price changes over the past 10 days. We want to find the last appearance of the price 236 in the “Price” column (column B) and retrieve the corresponding date from column A.
To achieve this, we can use the LOOKUP function. If the search key is found, it will return the last matching value. If the search key is not present, it will consider the value that is immediately smaller than the search key and present in the lookup range.
Find the Last Matching Value in Unsorted Data in Google Sheets (Solution 1)
The LOOKUP function is highly efficient for finding the last matching value in Google Sheets, even with unsorted data. Let’s explore two methods:
Method 1: By Using 1 and #DIV/0! in Lookup Range
Suppose we want to find the last row where the price 236 is found in the range B2:B11 and return the corresponding date from the range A2:A11. We can use the following LOOKUP formula:
=ARRAYFORMULA(LOOKUP(2, 1/(B2:B11=236), A2:A11))
This formula works by dividing 1 by the logical expression (B2:B11=236), which returns TRUE or FALSE. By dividing this formula by 1, we get 1 for TRUE and #DIV/0! for FALSE. The LOOKUP function will find the last matching value (1) and return the corresponding date from the result range.
Method 2: By Virtually Sorting the Lookup Search_Range and Result_Range
Another method to find the last matching value in Google Sheets is by virtually sorting the lookup range and the result range. Here’s the formula:
=IF(MATCH(E4, B2:B11, 0), TO_DATE(LOOKUP(E4, SORT(B2:B11), SORT(A2:A11, B2:B11, 1))))
This formula sorts the lookup range (B2:B11) in ascending order using the SORT function. It then sorts the result range (A2:A11) based on the sorted lookup range. Finally, it uses IF + MATCH to execute the formula if there is an exact match of the search key available in the lookup range.
Find the Last Matching Value in Sorted Data in Google Sheets (Solution 2)
If your data is already sorted, finding the last matching value becomes simpler. You can use the following LOOKUP formula:
=IF(MATCH(E4, B2:B11, 0), LOOKUP(E4, B2:B11, A2:A11))
Again, the IF + MATCH ensures that the LOOKUP formula is executed only if the search key is present in the search range.
XLOOKUP: The New Google Sheets Function to Find the Last Matching Value
Although XLOOKUP wasn’t available when this tutorial was first written, it is now a powerful tool for finding the last matching value. It simplifies the process significantly.
Here’s an example of its syntax:
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
To find the last matching value, whether your data is sorted or unsorted, you can use this formula:
=XLOOKUP(E4, B2:B11, A2:A11, "Missing", 0, -1)
Feel free to replace “Missing” with any custom text you prefer.
And there you have it! With these handy tips and functions, you can easily find the last matching value in Google Sheets. Happy data crunching!
To explore more Google Sheets tips and tricks, visit Crawlan.com.
