How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Saturday

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

Posted by   on

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 2nd 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?


Vlookup Range to Unpivot
image_1

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.


























Maria5
Maria41
Maria32
Maria22


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


Unpivot Range for Vlookup and Get the Last Non-blank Value in a Row
image_2

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

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

Example Sheet 100921


Related Resources:

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!