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.

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)

Related Topics

• VLOOKUP in Google Sheets – 10 Formula Variations, Tips, and Tricks
• How to Return an Entire Column in HLOOKUP in Google Sheets
• How to Use Multiple Conditions in HLOOKUP in Google Sheets
• HLOOKUP to Search Entire Table and Find the Header in Google Sheets
• Move Single Column to Multiple Columns Using HLOOKUP in Google Sheets