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.

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

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

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

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.