In this new tutorial, you can learn how to use Vlookup to get the last non-blank value/item in a row in Google Sheets.

How is this different from a regular VLOOKUP formula in Sheets?

Usually, we require to specify a cell in the lookup row to return the result. It’s called index (relative position of the output column) in Vlookup’s terms.

For example, we can use the following Vlookup in Google Sheets to search A2:E4 for the name “Prashant” in A2:A4 and return a value from the 2^nd column from the row found.

=vlookup("Prashant",A2:E4,2,0)

In this Vlookup, the index number (specified cell to get the result in the row) is 2. So we will get the value 10 which is in cell B3.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

What about using Vlookup to get the last non-blank value in the found row in Google Sheets?

You may get it using several combinations that most commonly involve Query, Filter, Index, etc.

The formula that I am going to write is also a combination one.

It involves the functions Vlookup, Query, Split, Flatten, and Column.

The advantage of using my formula is, it helps you search multiple criteria in the first column and get an array result in rows.

I mean, I have a Vlookup array formula to get the last non-blank values in rows in Google Sheets.

Vlookup Array Formula to Get the Last Non-blank Values in Rows

Since Vlookup in Google Sheets doesn’t have the feature to return value from the last non-blank cell in the found row, we should follow a workaround.

I mean, we require to manipulate the Vlookup array/range first in a particular way.

Formula Logic as per Sample Data

We should modify the data in A2:E4 into a three-column array in a specific way.

Note:- We can use a simple formula for that (I will come to that core formula part later).

1. In the modified array, the first column will contain the names “Ben”, “Prashant”, and “Maria”.
2. The second column will contain column numbers of the values in B2:E4.
3. The the third column will contain the values in B2:E4.

The three-column table will be sorted in descending order based on the column numbers in the second column.

Explanations to the Points 1 to 3 Above:

If we arrange the data accordingly using a formula, the third row of the sample data in the range A4:E4 corresponding to “Maria” will be similar to the below table.

From this, we must filter out blanks in the third column.

So if we Vlookup “Maria” and get value from the third column, we will get 1.

You may scroll up and see her record on the image.

You will understand the value returned by Vlookup is from the last non-blank cell.

Manipulating the data as above is not too complicated.

You can use my following Unpivot (combine + split + flatten) method for that.

Unpivot Data Range for Vlookup

To unpivot, we should combine the range in a specific way, flatten, and then split.

Please see the bold part in the following formula that’s unpivoting.

Unpivot_and_Format_Formula:

=ArrayFormula(
     Query(
        split(
           flatten(A2:A4&"|"&column(B1:E1)&"|"&B2:E4)
        ,"|"),
     "Select * where Col3 is not null order by Col1 asc, Col2 desc",0
     )
)

The QUERY in the outer part does the following (data formatting) things.

1. Filters out the blank cells in the third column.
2. Sorts the first column in ascending order (A-Z) and then the second column in descending order (largest to smallest).

The second column contains the data, which is not part of our original Vlookup range, i.e., A2:E4.

As I have mentioned above, it’s the column numbers of the non-blank values in B2:E4. It’s merely for sorting purposes.

Using Query, we have sorted the data in descending order based on this column.

It helps us Vlookup in this three-column range to get the last non-blank value in rows in Google Sheets.

Vlookup Fromula to Get the Last Non-blank Value in a Row in Google Sheets

You can follow the below Generic Formula:

=Vlookup(search_key,Unpivot_and_Format_Formula,3,0)

Replace the Unpivot_and_Format_Formula with the corresponding formula and search_key with “Prashant” to get the last non-black value in row#2 in the range A2:E4, i.e., 20.

Want to use multiple criteria?

Here is an example (the bold part is my above Unpivot_and_Format_Formula).

The search keys are in G2:G4.

=ArrayFormula(
     vlookup(
        G2:G4, 
        Query(
           split(
              flatten(A2:A4&"|"&column(B1:E1)&"|"&B2:E4)
           ,"|"),
        "Select * where Col3 is not null order by Col1 asc, Col2 desc",0
        )
        ,3,0
     )
)

Example Sheet 100921

Related Resources:

• How to Skip Blank Cells in Vlookup in Google Sheets.
• Move Index Column If Blank in Vlookup in Google Sheets.
• Lookup to Find the Last Occurrence of Multiple Criteria in Google Sheets.
• Vlookup from Bottom to Top in Google Docs Sheets.
• Vlookup Last Record in Each Group in Google Sheets.
• How to Lookup First and Last Values in a Row in Google Sheets.
• 4 Formulas to Last Row Lookup and Array Result in Google Sheets.
• How to Get LOOKUP Result from a Dynamic Column in Google sheets.
• Filter Vlookup Result Columns in Google Sheets (Formula Examples).
• Find the Last Non-Empty Column in a Row in Google Sheets.
• Google Sheets: How to Return First Non-blank Value in A Row or Column.
• Find the Last Non-Empty Column in a Row in Google Sheets.
• Count From First Non-Blank Cell to Last Non-Blank Cell in a Row in Google Sheets.