How to Lookup First and Last Values in a Row in Google Sheets

Lookup and Hlookup are the two functions that I am going here to use to Lookup first / last values in a row in Google Sheets. The purpose is to get the corresponding values from the header row.

To Lookup the last value in a row I’ll use the LOOKUP function and to Lookup the first value in a row, the suitable function is HLOOKUP.

I know you are expecting a real-life example before proceeding to the two Lookup formulas. Here you go!

The scenario is like this. I have a header row contain dates in sequential order (date of receipt of payments). Below that, in each row, some of the cells contain values, i.e. payments.

I just want to get the first and last payment receipt dates in each row. The animated screenshot (GIF image) below illustrates it even better.

Lookup First/Last Values in a Row in Google Sheets

In cell A2 I have an Hlookup formula to Lookup the first value in the row D2:I2, which is 500, and return the corresponding header from D1:I1, which is 03-Mar-20.

Similarly, in cell B2 I have a Lookup formula to Lookup the last value in that row, i.e. 250, and return the corresponding header, i.e. 05-Mar-20.

The Lookup formulas should automatically find the first and last values in the row. Then only it can return the dates from the header. How to do that?

Hlookup Function to Lookup First Value in a Row in Google Sheets

The following Hlookup formula is in the cell A2 in my sheet which copied down.

=ArrayFormula(ifna(hlookup(TRUE,{1/D2:I2<>"";$D$1:$I$1},2,0)))

The above formula is to Lookup the first value in row # 2 and returns the header from row # 1.

Formula Explanation

Here is the Hlookup syntax for your quick reference.

HLOOKUP(search_key, range, index, [is_sorted])

Here are the arguments used as per the formula above.

search_key: TRUE
range: {1/D2:I2<>"";$D$1:$I$1}
index: 2
is_sorted: 0

The Role of IFNA and ARRAYFORMULA in Hlookup

The function IFNA is for removing errors, specifically the #N/A! error, in the output in case the lookup row is blank.

The outer ARRAYFORMULA is for populating a virtual ‘range’. Didn’t get?

In order to get the ‘range’, i.e. {1/D2:I2<>"";$D$1:$I$1}, to work correctly we must use the ARRAYFORMULA function with it. You can use the ARRAYFORMULA with the range only or with the whole formula. I followed the latter.

Now let me explain how the formula helps you Lookup first value (first non-empty cell) in a row and return the header in Google Sheets.

For explanation purposes, I have inserted the below ‘range’ formula in cell D10.

=ArrayFormula({1/D2:I2<>"";$D$1:$I$1})
Hlookup First Non-Empty Cell - Google Sheets

Please check the formula output which is the virtual ‘range’ in Hlookup.

If you check my Hlookup formula, that Lookup the first value in Google Sheets, you can see that the search_key is the Boolean value TRUE. Why it’s so?

As per the above range (D10:I11), or you can say the virtual range, all the non-empty cells corresponding to the row # 2 (D2:I2) contains the Boolean TRUE values.

Now to the logic of the formula.

Hlookup in an unsorted range, see I’ve used is_sorted as 0 in the formula means unsorted, will perform an exact match in the range.

If there are multiple matching values (here multiple TRUE values) in the Hlookup range, the content of the cell (header) corresponding to the first value found is returned.

Lookup Function to Lookup Last Value in a Row in Google Sheets

The following Lookup formula is in the cell B2 which copied down.

=ArrayFormula(ifna(lookup(TRUE,1/D2:I2<>"",$D$1:$I$1)))

The above formula is to Lookup the last value (last non-empty cell) in a row and to return the corresponding value from another row, which is row 1.

Formula Explanation

Here are the Lookup syntaxes:

Syntax 1:

LOOKUP(search_key, search_range, [result_range])

Syntax 2:

LOOKUP(search_key, search_result_array)

In my example, I have used the Hlookup Syntax 1. So based on that, here are the arguments used to Lookup the last value (non-empty cell) in row # 2 and return the header.

search_key: TRUE
search_range: 1/D2:I2<>""
result_range: $D$1:$I$1

The purpose of using IFNA and ARRAYFORMULA is already explained in the first example. The same is applicable here also.

I have keyed the range formula in cell D10 to just to show you the result.

Lookup Last Non-Empty Cell - Google Sheets

You have seen the use of the search_key, which is Boolean TRUE, in the Hlookup example above.

Now please note that we have used the Lookup Syntax-1 which is called the result_range method. In this method, the result will be from the result_range, i.e. D1:I1.

That means the search_key TRUE matches twice in the ‘range’ and the last column contains the match is in column H. So the corresponding value from the result_range (row 1) returned.

Want the formula as per Syntax 2, i.e. in search_result_array, method?

Simply modify the Hlookup formula as below and use it in cell B2 instead of the above result_range formula.

=ArrayFormula(ifna(lookup(TRUE,{1/D2:I2<>"";$D$1:$I$1})))

That’s all about how to Lookup first and last values (first and last non-empty cells) in a row in Google Sheets.

Related Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.