To find the last matching value in Google Sheets is useful if you want to find the last occurrence of a specified value in a list.
Table of Contents
You might want to learn how to do it if you are using a constantly changing, dynamic sheet that you update regularly, and you need information about the latest occurrence of something.
Let’s take an example.
Say you deliver a daily lunch menu, and you want to create a sheet where you save the type of food you served each day. 🥪🍕
You want to search for food types and see easily when was the last time you served that food.
So how do we do that?
It’s easy. We need to find the last matching value in the list.
The combination of the
LOOKUP and the
SORT functions help us do that. It searches through a sorted row or column for a key value and then returns the value of the cell. The value of the cell is in a result range located in the same position as the search row or column.
Now don’t worry if you do not understand what anything means. By the end of this post, you will have gained a greater understanding of all this, so bear with us. 😅 But first, let’s talk about the LOOKUP function to understand how it works before we move any further.
The Anatomy of the LOOKUP Function
The syntax (the way we write) the
LOOKUP function is as follows:
=LOOKUP(search_key, search_range, result_range)
Let’s break this down so we can get a better understanding of what each of those terms mean:
=the equal sign is how we start just about any function in Google Sheets.
LOOKUPis our function. We will have to provide it with the search_key, search_range and result_range to make it work.
search_keyis the value that we want to search for in the selected row or column.
search_rangeis the range of cells where we search for the
result_rangewill be the range of cells, from which the formula returns a result. The value returned corresponds to the location where
search_keyis found in
⚠️ Note About the Syntax of LOOKUP
Be aware that this is not the only way to write the
LOOKUP function and exists another syntax of this function.
The other one is used for cases when you want to get the result from the same range than your search range. But for our example now, we use this above version to find the last matching value.
A Real Example of Using LOOKUP Function
Have a look at the example below to see how to use
LOOKUP to find the last matching value in the list. Let’s see the sheet of our food delivery service.
The above image shows how to use the
LOOKUP function together with the
SORT function to find the date where a type of food was last served. The function is as follows:
=LOOKUP(E2, SORT(B2:B11), SORT(A2:A11,B2:B11,TRUE))
Now, why do we need the
The reason is that the
LOOKUP function only works with sorted data.
So we need to sort our ranges to be able to use them in
LOOKUP. Furthermore, it is crucial that we sort our ranges in the same order. The
SORT function is the way to do this. If you would like to read the details on how to use
SORT function in Google Sheets, check out our guide.
Here’s what this example does:
- We wrote our
LOOKUPfunction with the three variables separated by commas.
- We have written “Pizza”, the term we want to look up, in the cell E2. This can be any food from the list.
- The first variable we need to write in the
LOOKUPformula is the
search_key, so this is the term we want to lookup in the column. We used a cell reference here, so we wrote E2 because this cell has our search key.
- The second variable of the
LOOKUPfunction is the
search_range. Therefore, we put the range of B2:B11 here, because this range is where we want to search for the
- We also had to sort the range in ascending order (the default search order of
SORT) to make it work with the
LOOKUPfunction, so the second variable is: SORT(B2:B11).
- The third variable of the
LOOKUPfunction is the
result_range, so the range where we want to get our result from. We must sort the
result_rangeaccording to the
search_range, so we had to write a
SORTformula here as well.
- The range we sort is the column containing the dates (
SORT). The range by which to sort the data is the column with the food names (B2:B11). Finally, we define the ascending order by adding TRUE at the end. The whole
result_rangelooks like this: SORT(A2:A11, B2:B11, TRUE).
- In summary, we added these three variables into the
LOOKUPfunction, and after writing the whole formula in cell E3, we got our result: “3/11/2020” which is the date where we last had pizza in the list.
- We can now change the search term to any other food from the list, and the formula will automatically give us the last matching date.
See how easy it is?
Make a copy of the spreadsheet from the link I’ve attached below and try it for yourself:
How to Find the Last Value in Each Row in Google Sheets
Here you can see the step-by-step process on how to find the last matching value in Google Sheets with the
LOOKUP function combined with
- To start, write the search term that you want to look up in any cell. For this guide, I write “Pizza” in cell E2.
- Next, select any cell where you want to show your result of the search. This is the cell where you will write your
LOOKUPformula. For example, choose the cell E3 by clicking on it.
- Enter the equal sign ‘=’ to begin the function. Then followed by the name of the function which is ‘
lookup‘ (or ‘
LOOKUP‘, whichever works).
- Great job! You should now see the pop-up auto-suggest box with the name of the function
- After having entered the opening bracket ‘(‘, you will have to add the corresponding variables for the function to work. Firstly, add the first variable, which is the key value we are looking for. Click on the cell that has the
search_key. That is, the cell E2 for me.
- After that, you need to add the
search_rangevariable which should be the sorted list of the food names. Start by writing the name of the
SORTfunction and select it from the auto-suggest box. Make sure to choose the right function, because there are more functions with similar names.
- Now, put the range of cells in the
SORTfunction where your food names are located. In my example, this is the range B2:B11, but since the list might be expanded later, I will include the whole column B starting from B2 and below. So the range I write here is B2:B.
- After closing the brackets on this
SORTfunction, put a comma to separate it from the third variable of the
- Now, write your third variable which is the
result_range. This should also be a sorted list containing the dates (the possible results we might get). So write another
- Put the range that has the possible results, so the range of the dates into it first. In the same way as before, this is A2:A for me because I want to include the whole column A where the future values will be added.
- Afterwards, highlight the range of cells by which you want to sort the dates (the range of the food names, B2:B in my sheet).
- Finally, set the ascending order of this sorting by writing TRUE at the end of the formula.
- To finish, hit the Enter key to close the brackets and get the result of the formula. That’s it! You can see the last matching value in cell E3.
That’s pretty much it! You can now use the
SORT functions to find the last matching value in Google Sheets. You can even pair what’ve you learnt together with the other numerous Google Sheets formulas to create even more powerful formulas. 🙂
Great explanation and examples! But a MAJOR drawback is that LOOKUP does //not// provide an exact match.
From the LOOOKUP help:
“If search_key is not found, the item used in the lookup will be the value that’s immediately smaller in the range provided. For example, if the data set contains the numbers 1, 3, 5 and search_key is 2, then 1 will be used for the lookup.”
That is brilliant!
But is it possible to make it work if your table goes horizontally rather than vertically?
When I try that, I get an error “LOOKUP evaluates to an out of range row value 16. Valid values are between 0 and 1 inclusive.”
where 16 is the position that I’m looking for.
I’m using the formula =LOOKUP(“Y”,sort(D2:2),sort(D1:1,D2:2,true)), which as far as I can tell is exactly the same structure but transposed.
Awesome, but does not work with data sets over a certain size, as Google Sheets gets stuck on “Calculating” and never finishes.
Hi can you advise how to do this but instead of looking for last instance, make it so it finds the first instance?