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 the`search_key`

.`result_range`

will be the range of cells, from which the formula returns a result. The value returned corresponds to the location where`search_key`

is found in`search_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 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
`LOOKUP`

function is the`search_range`

. Therefore, we put the range of**B2:B11**here, because this range is where we want to search for the`search_key`

. - We also had to sort the range in ascending order (the default search order of
`SORT`

) to make it work with the`LOOKUP`

function, so the second variable is:**SORT(B2:B11)**. - The third variable of the
`LOOKUP`

function is the`result_range`

, so the range where we want to get our result from. We must sort the`result_range`

according to the`search_range`

, so we had to write a`SORT`

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 whole`result_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 the`SORT`

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 the`LOOKUP`

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 another`SORT`

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. 🙂

## 5 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?

Thank you!