How to Perform a Reverse HLOOKUP in Google Sheets

Reverse HLOOKUP in Google Sheets is possible. To do this, you only need to rearrange the rows ‘virtually.’ I mean there are no physical changes to the row positions—just adjustments within the HLOOKUP formula itself. How?

I’m shedding some light on this topic today. See below how to perform a reverse HLOOKUP in Google Sheets.

Reverse HLOOKUP in Google Sheets – What Is It?

Normally, HLOOKUP searches across the first row for a key. In a reverse HLOOKUP, instead of the first row, we can use any other row. But remember, there is no dedicated reverse HLOOKUP function!

How is reverse HLOOKUP possible in Google Sheets?

For example, we can use HLOOKUP to search across the third row and return a value from the first row by rearranging the data within HLOOKUP. It’s very simple.

Google Sheets Reverse HLOOKUP Formula Example

See the sample data below.

Reverse HLOOKUP example in Google Sheets

In this example, I want to find the ‘date of birth’ of “Student 2.” That means “Student 2” is the search key in HLOOKUP, but it’s located in the third row.

HLOOKUP can only search across the first row. To perform a reverse HLOOKUP in Google Sheets, first rearrange the data as shown below (this rearrangement happens within HLOOKUP):

{A3:E3; A1:E2}

Alternatively, you can use the following VSTACK formula:

VSTACK(A3:E3, A1:E2)

You should apply this as the range in your HLOOKUP formula. By doing so, Google Sheets virtually places the third row above the first row. Now, the row with the names becomes the first row, and the row containing the ‘date of birth’ becomes the second row.

In this new range, HLOOKUP becomes possible. Here’s the reverse HLOOKUP formula:

=HLOOKUP("Student 2", {A3:E3; A1:E2}, 2, FALSE)  

If you used the VSTACK version for rearranging the data, the formula would be as follows:

=HLOOKUP("Student 2", VSTACK(A3:E3, A1:E2), 2, FALSE)  

This formula searches across the first row for the student name “Student 2” and returns the date of birth from the second row, thanks to the rearranged data.

Note: Sometimes, formulas may return date values instead of formatted dates. If this happens, select the cells and apply Format > Number > Date to display the results correctly.

In Summary

Whenever you need to perform a reverse (or backward) HLOOKUP, simply rearrange the rows within your HLOOKUP formula.

As a side note, you can now use XLOOKUP, a modern function that allows you to specify the row to search and the result row separately, making the process much easier.

For example:

=XLOOKUP("Student 2", A3:E3, A1:E1)
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.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

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.