HLOOKUP Not Working? Fix Common Microsoft Excel Errors

If HLOOKUP is not working in Excel, first check that the value you want to find is in the top row of the selected table, that the row number points to a row inside that table, and that you have chosen the right match mode. For a precise match, use FALSE as the fourth argument; this avoids many wrong-result problems caused by HLOOKUP’s approximate-match default.

Start with the lookup row and table range

HLOOKUP searches horizontally: it looks across the first row of the table_array you provide, then returns a value from another row in the same column. It does not search every row in the selected range. If your lookup value is in the first column rather than the first row, HLOOKUP is the wrong function; VLOOKUP or another lookup method may be a better fit.

For example, suppose B1:F1 contains product IDs, B2:F2 contains product names, and B3:F3 contains prices. With the product ID to find in B5, this formula returns the price for P-104, which is 125 if P-104 is in E1 and E3 contains 125:

With product IDs in B1:F1, prices in B3:F3, and the ID to find in B5, use =HLOOKUP(B5,$B$1:$F$3,3,FALSE).

The range starts at B1, so its first row is the ID row. The number 3 means the third row within B1:F3, not worksheet row 3 in every possible range. If the table range starts at B2 instead, row 3 within that range is worksheet row 4. Check the range’s top-left cell and count down within the range when diagnosing an incorrect return.

Understand exact and approximate matching

The fourth HLOOKUP argument determines how Excel matches the lookup value. FALSE requests an exact match; TRUE requests an approximate match. If you omit this argument, Excel uses approximate matching. That default is a frequent source of results that look plausible but are wrong, particularly when the first row contains unsorted labels or product codes.

Use FALSE for identifiers, names, dates that must match precisely, and most ordinary lookup tasks. If Excel cannot find the exact value in the top row, it returns #N/A rather than silently choosing a nearby value. TRUE is appropriate for ordered thresholds, such as tax bands or grading cutoffs, when the first row is sorted in ascending order. With TRUE, Excel returns the value associated with the largest first-row entry that is less than or equal to the lookup value. If the first row is not properly sorted, the result may be unreliable.

For instance, with breakpoints 0, 50, 70, and 90 in B1:E1 and corresponding rates 0%, 2%, 4%, and 6% in B2:E2, a value of 76 should select the 70 breakpoint and return 4%:

With breakpoints in B1:E1, rates in B2:E2, and the score in B4, use =HLOOKUP(B4,$B$1:$E$2,2,TRUE). If B4 is 76, the result is 4%.

Fix #N/A when the value seems to be there

#N/A means an exact lookup did not find a matching value, or an approximate lookup could not find a suitable match. A value can look identical on screen and still differ underneath. Common causes include extra spaces, a number stored as text in one place but as a number in another, different date values, or a lookup range that starts one column too far to the right.

Check that the lookup value appears in the first row of the selected range, not just somewhere else in the table.
Compare the actual cells, not only their displayed appearance. Text “001” and the number 1, for example, are not the same value.
Look for leading or trailing spaces in text. TRIM can remove ordinary extra spaces, but it does not remove every possible nonprinting character.
Confirm that the table range includes the entire lookup row and the return row. If new columns were added after the formula was written, the original range might not include them.
For approximate matching, verify that the first row is sorted ascending and that the lookup value is not below the smallest breakpoint.

One way to reduce range mistakes is to use absolute references, as in the examples with dollar signs. Absolute references keep the range fixed when a formula is copied to other cells. They do not make the range expand automatically when new data is added, so review the range after changing the table.

Interpret #REF!, #VALUE!, and other errors

A #REF! error commonly indicates that the row index asks for a row beyond the height of the table range. If the table_array contains three rows, the row index can be 1, 2, or 3; 4 is outside the range. Remember that row 1 is the lookup row itself. Expand the table range or correct the index to return a row that exists.

#VALUE! can occur when the row index is less than 1. The row index must be a positive number identifying a row within the table. Check whether a reference used for that argument contains zero, a negative number, or unexpected text. #NAME? often points to a misspelled function name or a name in the formula that Excel does not recognize. A missing parenthesis or a wrong argument separator can also prevent a formula from being accepted; separator conventions can vary with regional settings.

If Excel displays a warning or an error different from these, inspect the formula bar and confirm that the formula begins with an equals sign, the cell references are valid, and the arguments are separated correctly for your installation. A formula copied from a website or another locale may use semicolons instead of commas. Do not replace separators blindly: use the separator Excel expects for your regional settings.

Diagnose a wrong answer without an error

HLOOKUP can return a value without an error and still return the wrong one. The first thing to inspect is the fourth argument. If it is omitted or TRUE, Excel may be doing an approximate match rather than looking for an exact label. Set it to FALSE when the lookup should identify one specific value.

Next, check the row index and the shape of the selected range. If the formula returns a name when you expected a price, it may be pointing to row 2 rather than row 3 within the range. If the returned value is from a neighboring column, inspect the lookup row for duplicate entries and confirm the lookup value’s exact type and contents. HLOOKUP returns a value from the column where it finds its match; it does not choose among duplicate matches based on the value in the return row.

Wildcards can also affect exact text matching. With FALSE, an asterisk (*) can match any sequence of characters and a question mark (?) can match one character. To search for a literal wildcard, precede it with a tilde, such as ~* for a literal asterisk. If a code contains these characters and they should be treated literally, account for this behavior when building the lookup value.

Check calculation and cell formatting

Sometimes the formula is correct but Excel is showing it as text or has not recalculated it. If the cell displays the formula itself instead of a result, check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign. Change the cell to General, then re-enter the formula; changing the format alone may not convert an existing text entry into a working formula.

If formulas appear throughout the worksheet, Show Formulas may be switched on. In Excel for Microsoft 365 on Windows, use Formulas > Formula Auditing > Show Formulas to toggle that view. The Windows shortcut is Ctrl+` (the grave-accent key); on Mac, Control+` is commonly used, though keyboard layouts can differ. You can also use the ribbon command. If results are stale, check Formulas > Calculation Options > Automatic. In Manual calculation mode, Excel may not update formulas until a recalculation is triggered.

Formatting affects what a result looks like, not which cell HLOOKUP selects. For example, a returned date serial may appear as a number if the result cell is formatted as General. Apply an appropriate date or number format if the underlying result is correct but displayed unexpectedly.

Use a practical troubleshooting sequence

Work through the checks in this order so you can isolate the cause rather than changing several parts of a formula at once:

Select the formula cell and read the formula bar. Confirm the function name, lookup value, table range, row index, and fourth argument.
Verify that the lookup value belongs in the first row of the selected range. Adjust the range if it begins on the wrong row or misses relevant columns.
Count the rows in the selected range and make sure the row index is between 1 and that count.
Use FALSE for an exact lookup unless you specifically need a sorted threshold lookup.
Compare the lookup value with the source cell for spaces, text-versus-number differences, dates, and duplicate entries.
Check whether the cell is formatted as Text, Show Formulas is enabled, or calculation is set to Manual.
Test the formula in a spare cell with a small, known example. Once it works, apply the corrected logic to the original data.

For a formula copied across or down a worksheet, also review relative references. A reference such as B1:F3 can shift as the formula is copied. Dollar signs lock the relevant row or column. This is a copying issue rather than a special HLOOKUP rule, but it often explains why a formula works in one cell and fails in the next.

When to use another lookup function

HLOOKUP remains useful for simple tables whose lookup keys are arranged across the top. It can be less convenient when the table layout changes, when you need more flexible return ranges, or when the lookup direction is not horizontal. For a vertical lookup, VLOOKUP is the traditional counterpart. For newer workbooks, XLOOKUP can search a horizontal row and return from a corresponding row, and it allows an explicit value to show when no match is found.

With lookup IDs in B1:F1, prices in B3:F3, and the ID to find in B5, this XLOOKUP formula returns the matching price or the text Not found if no match exists:

With IDs in B1:F1, prices in B3:F3, and the search ID in B5, use =XLOOKUP(B5,B1:F1,B3:F3,"Not found"). For P-104 in the earlier example, the result is 125.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older Excel releases. Another option is INDEX with MATCH, which works for a horizontal lookup and can be used in older versions. With prices in B3:F3, IDs in B1:F1, and the search ID in B5, use =INDEX(B3:F3,MATCH(B5,B1:F1,0)); for P-104, it returns 125. The zero tells MATCH to find an exact match. Changing functions will not fix bad source data or a mismatched value, so resolve those issues first.

Frequently Asked Questions

Why does HLOOKUP return #N/A even though the value is visible?
The displayed text may include spaces or hidden characters, or one value may be text while the other is numeric. Also confirm that the visible value is in the first row of the table range and that the formula uses the intended match mode.

Does HLOOKUP search the whole table?
No. It searches only the first row of the table_array. After finding a match there, it returns a value from the row specified by the row index, in the same column as the match.

Why does HLOOKUP return the wrong column?
Approximate matching may be enabled, the lookup row may contain duplicates, or the lookup values may not be equivalent despite looking similar. Use FALSE for an exact match and check the range and source values.

What does the row number in HLOOKUP mean?
It is the row’s position inside the selected table range, starting with 1 for the top row. It is not necessarily the worksheet row number. The lookup row counts as row 1.

Can HLOOKUP search a row from right to left?
HLOOKUP searches across the first row from left to right and returns from the matching column. It is not designed to reverse the lookup direction. Consider XLOOKUP or INDEX and MATCH if your layout requires a different arrangement.

Why is Excel showing my HLOOKUP formula instead of a result?
Show Formulas may be enabled, or the cell may be formatted as Text or contain a leading apostrophe. Turn off Show Formulas if needed, set the cell to General, and re-enter the formula.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.