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.LOOKUP
is our function. We will have to provide it with the search_key, search_range and result_range to make it work.search_key
is the value that we want to search for in the selected row or column.search_range
is the range of cells where we search for thesearch_key
.result_range
will be the range of cells, from which the formula returns a result. The value returned corresponds to the location wheresearch_key
is found insearch_range
.
⚠️ 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 SORT
function?
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
LOOKUP
function 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
LOOKUP
formula is thesearch_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
LOOKUP
function is thesearch_range
. Therefore, we put the range of B2:B11 here, because this range is where we want to search for thesearch_key
. - We also had to sort the range in ascending order (the default search order of
SORT
) to make it work with theLOOKUP
function, so the second variable is: SORT(B2:B11). - The third variable of the
LOOKUP
function is theresult_range
, so the range where we want to get our result from. We must sort theresult_range
according to thesearch_range
, so we had to write aSORT
formula 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 wholeresult_range
looks like this: SORT(A2:A11, B2:B11, TRUE). - In summary, we added these three variables into the
LOOKUP
function, 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 SORT
.
- 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
LOOKUP
formula. 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
LOOKUP
.
- 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_range
variable which should be the sorted list of the food names. Start by writing the name of theSORT
function 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
SORT
function 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
SORT
function, put a comma to separate it from the third variable of theLOOKUP
formula.
- 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 anotherSORT
formula.
- 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 LOOKUP
and 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. 🙂

4 comments
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?