How to Find the Last Value in Each Row in Google Sheets

Last Value in Each Row in Google Sheets
How to Find the Last Value in Each Row in Google Sheets – Sheetaki

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.

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:

 

 

How to Find Last Value in Each Row in Google Sheets

  1. To start, simply click on a cell to make it the active cell. For this guide, I will be selecting F2, where I want to show my result of row 2.

Select an active cell

 

  1. Next, type the equal sign ‘=’ to begin the function and then followed by the name of the function which is ‘lookup‘ (or ‘LOOKUP‘, whichever works).

 

  1. Great! You should now find that the auto-suggest box will pop-up with the name of the function LOOKUP.

Find The Last Value in Each Row in Google Sheets

 

  1. After the opening bracket ‘(‘, you have to add the variables. Depending on which solution you use, you can add 2 or 3 variables. For this guide, I will go through the steps of Option #2, but you can do the same with the variables of Option #1 as well.

 

  1. Add the first variable, which is the key value we are looking for. We look up 1, so this is the first variable.

Find The Last Value in Each Row in Google Sheets

 

  1. After that, add the search_range variable, which is the ARRAYFORMULA converting the cells into numbers. Start writingARRAYFORMULA and add the bracket to start writing its content.

ARRAYFORMULA and LOOKUP Functions

 

  1. Now we write the cell range where we want to perform the LOOKUP function. It is B2:E2 when we work on row 2.

Using LOOKUP Function in Google Sheets

 

  1. To check whether those cells in the row are empty or not, we include the not-equal expression that returns TRUE when the cell is not empty and FALSE when it is empty. This expression is B2:E2 <> “”.

Find The Last Value in Each Row in Google Sheets

 

  1. Now we divide 1 by this expression to convert the TRUE and FALSE values into a form that the LOOKUP function can handle.

Find The Last Value in Each Row in Google Sheets

 

  1. After the second variable is done, add the third variable which is result_range. It is the range of cells that you use for the lookup, so the range B2:E2.

Find The Last Value in Each Row in Google Sheets

 

  1. Finally, close the brackets and hit Enter. You can see the last name of row 2.

Find The Last Value in Each Row in Google Sheets

 

  1. At last, drag down this function to the rest of the rows to apply it to the other rooms.

Find The Last Value in Each Row in Google Sheets

 

That’s it, good job! You can now use the LOOKUP function to find the last value in each row in Google Sheets. Feel free to use it together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂

 

Get emails from us about Google Sheets.

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.

 

0 Shares:
1 comment
  1. well explained. I have a question. What if I want to get the last number if this range has both strings and numbers? What kind of changes do I need to do?

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like