Excel Reverse HLOOKUP: Formulas and Better Alternatives

Formulas & functions · Updated November 18, 2026

Excel has no separate “reverse HLOOKUP” function. If you mean looking up a value in one row and returning the corresponding value from the row above it, use XLOOKUP in Microsoft 365 or Excel 2021 and later, or use INDEX and MATCH; if you mean searching a row from right to left, XLOOKUP can do that too. HLOOKUP itself only returns values from rows at or below its lookup row.

What “reverse HLOOKUP” can mean

The phrase is used for two different tasks, and choosing the right one matters. In a conventional HLOOKUP, Excel searches across the first row of a selected range, finds a matching column, and returns a value from a row beneath it. A reverse lookup often means returning a value from a row above the row containing the match. Another possible meaning is searching from the rightmost column toward the leftmost column.

These are separate directions. Returning a value from above changes which row supplies the answer; searching right-to-left changes the order Excel checks for a match. HLOOKUP cannot return a value above its lookup row, and it does not offer a setting to reverse its search order. XLOOKUP handles either task by letting you specify lookup and return ranges directly, and can also search in reverse order.

For a simple example, imagine location names in B1:G1 and their weekly totals in B2:G2. The totals are 120, 95, 110, 80, 105, and 130, in that order. If you have the total 80 and want its location, you need to look in the lower row and return the corresponding entry above. That is the “return from above” version of a reverse HLOOKUP.

Return a value from the row above with XLOOKUP

XLOOKUP is usually the clearest solution in Microsoft 365 and Excel 2021 or later. It takes a lookup value, the row or column to search, and the corresponding row or column to return. The lookup and return ranges can be in either vertical or horizontal orientation, and the return range can be on either side of the lookup range.

With location names in B1:G1, totals in B2:G2, and the value to find in B5, enter:

=XLOOKUP(B5,B2:G2,B1:G1,"Not found")

If B5 contains 80, this returns West, assuming West is in E1 and 80 is in E2. XLOOKUP uses exact matching by default, so a number such as 80 will not be treated as an approximate match. The optional “Not found” argument supplies a readable result if Excel cannot find the value, rather than displaying an error.

The key is not to reverse the HLOOKUP row number or try to use a negative number. Instead, identify the row that contains the value to find and specify the row you want returned. XLOOKUP keeps those roles explicit, which makes formulas easier to inspect when a sheet has several horizontal data rows.

Search from right to left

If the lookup values are in the top row and the matching result is below, but you want Excel to begin searching at the far right, XLOOKUP has a search-mode argument for that. This is useful when values may repeat and the rightmost matching entry should take priority. A reverse search does not change which row is returned; it only changes the order in which Excel checks for a match.

With location names in B1:G1, totals in B2:G2, and the location to find in B5, this formula searches the names from right to left:

=XLOOKUP(B5,B1:G1,B2:G2,"Not found",0,-1)

The 0 specifies exact match, and -1 as the final argument tells XLOOKUP to search from last to first. If B5 contains West, the result is 80. If every location name is unique, a left-to-right search would return the same result; the reversed search order makes a difference when there are duplicates.

For example, if the same product code appears in multiple columns because the sheet records successive updates, a normal XLOOKUP returns the first match in the stated search direction. Setting the search mode to -1 makes it return the last occurrence in the range. It does not sort the data, reverse the worksheet, or change the stored values.

Use INDEX and MATCH as a compatible alternative

For versions of Excel without XLOOKUP, INDEX and MATCH can look up a value in one horizontal row and return the aligned value from another row, including a row above it. MATCH finds the position of an exact match; INDEX uses that position to retrieve the corresponding item from the return row. This approach is available in older Excel versions as well as current ones.

With location names in B1:G1, totals in B2:G2, and the total to find in B5, use:

=INDEX(B1:G1,1,MATCH(B5,B2:G2,0))

If B5 contains 80, MATCH finds the fourth position in B2:G2, and INDEX returns the fourth item in B1:G1: West. The final 0 in MATCH requests an exact match. INDEX’s row argument is 1 because the return range consists of a single row; the MATCH result supplies the column position.

This formula is also useful when a workbook must work for people using older Excel releases. Its drawback is that it takes more effort to read than XLOOKUP, particularly when ranges are large or nested inside other calculations. Keep the lookup and return ranges the same width so their positions stay aligned.

What HLOOKUP can and cannot do

HLOOKUP remains useful when the lookup values are in the first row of a table and the answer is below them. It searches across that first row and returns a value from a row number you specify. The row number is counted within the selected table range: 1 means the lookup row itself, 2 means the row immediately beneath it, and so on. It cannot use a negative row number to retrieve information above the lookup row.

With location names in B1:G1, totals in B2:G2, and a location to find in B5, a conventional exact-match formula is:

=HLOOKUP(B5,B1:G2,2,FALSE)

If B5 contains West, this returns 80. FALSE requests an exact match. Including that argument is important: if the fourth argument is omitted or set to TRUE, HLOOKUP uses approximate matching, which expects the first row to be sorted in ascending order and can return an unintended result when it is not.

Changing the row number to 0 or a negative value does not make HLOOKUP search upward. To return a value from a row above, use XLOOKUP or INDEX and MATCH. To search for the last matching item rather than the first, use XLOOKUP’s reverse search mode or choose another approach designed for your Excel version.

Choose the right formula for your layout

Start by describing the job in terms of the lookup row, return row, and desired match behavior. “Find 80 in the totals row and return the location above it” calls for a lookup range below the return range. “Find West in the location row, return its total, and prefer the rightmost duplicate” calls for a right-to-left search. Keeping these requirements separate prevents a common mistake: reversing the lookup direction when the actual need is to return from above.

  • Use XLOOKUP when the workbook uses Microsoft 365 or Excel 2021 and you want a direct, readable formula. It supports either row order and can search from last to first.
  • Use INDEX and MATCH when you need compatibility with older Excel versions or want to keep using a widely supported lookup pattern.
  • Use HLOOKUP when the lookup values are in the first row of the table and the answer is in a row below it. Include FALSE for an exact match unless approximate matching is intentional.
  • Consider restructuring the data if you repeatedly need complicated lookups across many horizontal rows. A conventional table with one record per row is often easier to filter, extend, and analyze.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not included in some older perpetual versions. A workbook containing XLOOKUP may show a #NAME? error when opened in an Excel version that does not recognize the function. Check the Excel version used by everyone who needs to open or edit the workbook before choosing it.

Common errors and how to diagnose them

A correct-looking formula can still return an error or the wrong item if the data is inconsistent. Check the range boundaries and the type of the values before changing the formula. In horizontal lookups, a single shifted column in either range is enough to return a plausible but incorrect result.

  • #N/A: The lookup value was not found. Check for extra spaces, different spellings, or a number stored as text in one place and as a number in another. With XLOOKUP, the optional not-found argument can display a message, but it does not fix a mismatch in the source data.
  • Wrong duplicate match: XLOOKUP normally returns the first match it encounters. If the rightmost duplicate is the desired one, set its search mode to -1. Decide which occurrence is meaningful before assuming a lookup result is wrong.
  • Misaligned return: Make sure the lookup and return ranges cover the same set of columns in the same order. For INDEX and MATCH, both ranges should have equal widths.
  • Unexpected HLOOKUP result: Verify that the fourth argument is FALSE for an exact match. Approximate matching can produce a result that looks valid even when the lookup value is absent.
  • Text and number differences: A cell showing 80 may contain text rather than the numeric value 80. Check the source cells and standardize the underlying data rather than relying only on how the cells look.

When a result seems wrong, test the lookup value directly against the source row, then confirm the matching column and the corresponding return cell. This simple check helps distinguish a formula issue from a source-data issue. Also review whether duplicate keys are expected: exact matching does not guarantee uniqueness.

Why horizontal lookups can be fragile

HLOOKUP and horizontal XLOOKUP formulas work with data arranged across columns, but many Excel tools are easier to use when each record occupies its own row and each field has a header. A list of locations and totals spread across one row can be compact for a small report, yet it becomes awkward when new locations are added, values are sorted, or the same lookup needs to work across multiple periods.

If the data is a growing list, consider arranging it as a two-column table with headings such as Location and Total. This structure makes each location a record and allows familiar filtering and sorting. It does not mean HLOOKUP is inherently wrong; it means that a formula should fit the structure and expected growth of the workbook. For a fixed-width report or a timeline laid out horizontally, horizontal lookup formulas can be entirely appropriate.

Frequently Asked Questions

Can HLOOKUP return a value from a row above?
No. HLOOKUP returns from the row specified within its table range, and that row must be at or below the first row containing the lookup values. Use XLOOKUP or INDEX and MATCH to look up a lower row and return the corresponding value above it.

How do I make HLOOKUP search from right to left?
HLOOKUP has no reverse-search option. In Microsoft 365 or Excel 2021 and later, use XLOOKUP with -1 as its search-mode argument to search from the last item toward the first. This changes the search order, not the row from which the result is returned.

Is reverse HLOOKUP the same as HLOOKUP with FALSE?
No. FALSE asks HLOOKUP for an exact match; it does not reverse the search direction and does not allow a result from above the lookup row. These settings solve different problems.

Can I use XLOOKUP for horizontal data?
Yes. The lookup and return arrays can both be horizontal rows. Put the row to search in the lookup-array argument and the corresponding row of results in the return-array argument. They should cover the same columns in matching order.

What if my Excel version does not have XLOOKUP?
Use INDEX and MATCH for a lookup that returns a value from above or below. Both functions are available in older Excel versions. If the task is a standard lookup from the top row to a row below, HLOOKUP with FALSE is another option.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.