# How to Find the Last Matching Value in Google Sheets

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.

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

1. To start, write the search term that you want to look up in any cell. For this guide, I write “Pizza” in cell E2.

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

1. Enter the equal sign ‘=’ to begin the function. Then followed by the name of the function which is ‘`lookup`‘ (or ‘`LOOKUP`‘, whichever works).

1. Great job! You should now see the pop-up auto-suggest box with the name of the function `LOOKUP`.

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

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

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

1. After closing the brackets on this `SORT` function, put a comma to separate it from the third variable of the `LOOKUP` formula.

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

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

1. 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).

1. Finally, set the ascending order of this sorting by writing TRUE at the end of the formula.

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

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

## How to Use TEXTJOIN Function in Google Sheets

The TEXTJOIN function in Google Sheets is useful if you want to concatenate or join values with a…

## How to Use LEFT Function in Google Sheets

The LEFT function in Google Sheets is useful to return a substring from the beginning of a specified…

## How to Compare Two Sheets in Google Sheets

Learning how to compare two sheets in Google Sheets is useful to identify matches and differences between two…

## How to Use the COUPNUM Function in Google Sheets

The COUPNUM function in Google Sheets is used to calculate the number of coupons (interest payments) that will…