How to Find Last Value in Each Row in Google Sheets [Solved]

To find the last value in each row in Google Sheet using the LOOKUP function is useful if you want to extract the last used column in your data set.

The Anatomy of the LOOKUP Function
A Real Example of Using LOOKUP Function
How to Find Last Value in Each Row in Google Sheets

Let’s take an example.

Say we have some rooms in a school and we keep track of the students who used these rooms for their activities. We want to easily see who was the last person using each room.

So how do we do that?

Easy. We need to find the last non-empty cell in each row.

The LOOKUP function helps us do that. It searches through a row or column for a key value and then returns the value of the cell in a result range located in the same position as the search row or column. You will gain a better understanding after a couple of examples. 🙂

Let’s dive into the examples to show you how we can write our own LOOKUP function in Google Sheets to find the last non-empty cell in each row.

The Anatomy of the LOOKUP Function

The syntax (the way we write) the LOOKUP function is as follows:

=LOOKUP(search_key, search_range|search_result_array, [result_range])

Let’s dissect this thing so we’d better understand what each term means:

= the equal sign is how we start any function in Google Sheets.
LOOKUP is our function. We will have to add the arguments into it to make it work.
search_key is the value to search for in the selected row or column.
search_range|search_result_array means that we have two options to choose from.
The first option is to use search_range and result_range (the third argument) together. In this situation, we search for the key in the search_range and we get output values from result_range.
The second option is to ignore result_range and only use search_result_array, where the formula searches the first row or column and returns the value from the last row or column in the array.
result_range is 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. It should not be used if using the search_result_array method.

The Behaviour of LOOKUP Function

To explain, let’s see closely how the LOOKUP function actually works.

For example, look at this example where

search_key is 3,
search_range here is an array containing numbers from 1 to 4,
result_range is another array containing text values from “a” to “d”.

=LOOKUP(3, {1,2,3,4}, {"a","b","c","d"})

The function searched for the value 3 in the array of numbers, finds the number 3, and returns the corresponding value from the result range. So the result is “c”.

If there are more than one matching values, the function always returns the last one that matches the search_key. Look at this function:

=LOOKUP(3, {1,2,3,3,3,4}, {"a","b","c","d","e","f"})

This function returns the corresponding value of the last found 3 value, which is the letter “e” in this example. Eventually, this behavior is what we are going to use to find the last value in each row!

But that’s not all. Let’s have a look at the case when none of the cells matches the search_key.

=LOOKUP(8, {1,2,3,3,3,4}, {"a","b","c","d","e","f"})

This function tries to look up the value 8 in the second array. However, there is no 8 in this range. In this case, the LOOKUP function searches for the immediately smallest value of the range, which is number 4 in this example. It returns the corresponding value of 4, which is “f” in this example.

A Real Example of Using LOOKUP Function

Look at the example below to see how LOOKUP function is used to find the last value in each row in Google Sheets.

Find The Last Value in Each Row in Google Sheets

Option #1: LOOKUP with a text value that is surely the largest value

=LOOKUP("zzz", B2:E2)

Here’s what this example does:

We wrote a LOOKUP function with two arguments separated by commas in row 2.
In fact, we use the behavior mentioned above when none of the cells in the range matches the search key.
We created a search_key “zzz” that is definitely later in a sorted list than any of our actual values (students’ names).
We add the search_result_array which is the whole row we are working on (row 2 in the example containing the cells between B2:E2).
Finally, we drag-down the function to apply it to the other rows of the data set as well.

Option #2: LOOKUP by converting the text values into numbers

=LOOKUP(1, ARRAYFORMULA(1/(B2:E2<>"")),B2:E2)

Here’s what this example does:

We wrote a LOOKUP function with three arguments separated by commas in row 2.
In this example, we match the content of the cells with numbers so that we can use the LOOKUP function with numbers instead of strings. This way, we don’t have to use a burnt-in “zzz” value but instead, create a more secure solution using numbers.
We work within a range of a row, so we use the B2:E2 range as the result_range and also inside the search_range, but this one needs some modification in order to work with numbers.
Firstly, look at the second argument, which is an ARRAYFORMULA function. We use this to match the content of the cells with numbers. The ARRAYFORMULA at the beginning of this argument ensures that we work with an array (the whole row) and not just with one cell.
Inside the brackets, there is the B4:E4<>”” expression. The <> operator means “not equal” and the “” (double quotes) means empty content since nothing is between the quotes. So this part of the formula checks whether a cell in a range is empty or not.
Now let’s explain why there is a division (1 / B4:E4<>””). In short, it’s needed to convert boolean values into numbers. The expression B4:E4<>”” returns either TRUE or FALSE. When we divide 1 by a cell containing TRUE value, we get 1 as a result because TRUE is considered as a 1 in Google Sheets. Similarly, when we divide 1 by FALSE (considered as a 0 in Google Sheets), we get a division error because we tried to divide by 0. In conclusion, when the cell is not empty, we get a 1. When the cell is empty, we get an error which is not a number, so we excluded these cells from our LOOKUP search.
Now we need to find the last cell that matches the search_key. We put 1 as the search_key because we are looking for those non-empty cells. The LOOKUP function searches for the search_key, and as we have seen above, it gives the last cell in the array that has the value 1.
The result_range contains the actual names, so the result of the whole function is the content of the last cell that is not empty. Great! We needed exactly that name.
Finally, we drag-down the function to apply it to the other rows of the data set as well.

Try it out by yourself. You may make a copy of the spreadsheet using the link I have attached below and try it for yourself:

Make a copy of example spreadsheet