#N/A in HLOOKUP: Incorrect Row Index Troubleshooting

An #N/A error in an HLOOKUP formula indicates that Excel cannot locate the specified lookup value within the very first row of your defined table range. While many users assume that an invalid row index number triggers an #N/A, this reflects a fundamental misunderstanding of Excel’s error types: an invalid or out-of-bounds row index generates a #REF! or #VALUE! error, whereas an #N/A strictly represents an unresolved lookup match.

Quick Answer

If your HLOOKUP returns #N/A, the lookup value does not exist in row 1 of your table range, or match settings are misconfigured. Set argument 4 to FALSE for an exact match: =HLOOKUP(lookup_value, table_range, row_index_num, FALSE). Ensure the top row of table_range contains the search term.

What You Need to Know: HLOOKUP Mechanics and Error Signposts

HLOOKUP searches horizontally across the top row of a matrix and returns data from the specified row index below it:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Understanding which error corresponds to which argument narrows your troubleshooting immediately:

• #REF! Error: Your row_index_num is greater than the total number of rows in table_array. For instance, asking for row 6 in a 4-row table returns #REF!.
• #VALUE! Error: Your row_index_num is less than 1 or contains non-numeric text.
• #N/A Error: Excel scanned the top row of table_array and could not find an acceptable match for lookup_value.

If you are receiving #N/A, your troubleshooting must focus on row 1 of your table array and the matching criteria, not the row index number.

The Key Factors Behind #N/A in Horizontal Lookups

1. Top Row Boundary Misalignment:HLOOKUP can only scan the first row of the selected range. If your search keys are located in Row 2 (for example, beneath a sheet title or header category in Row 1), but your range begins at Row 1 (e.g., A1:Z10), HLOOKUP searches Row 1 and fails to find your keys, throwing #N/A.
2. Unspecified Match Mode (range_lookup):
   When the 4th argument is omitted, HLOOKUP defaults to TRUE (approximate match). This mode requires the header row to be sorted in ascending order from left to right. If the headers are unsorted and the lookup value is smaller than the smallest value in the first cell, HLOOKUP instantly produces #N/A.
3. Data Type and Character Formatting:
   Mismatches between numbers formatted as text and true numbers will prevent an exact match. Similarly, extra space padding will break the search. For a direct comparison of matching rules, see #N/A in VLOOKUP: Exact Match (0/FALSE) vs. Approximate Match (1/TRUE).

Diagnostic & Troubleshooting Path

Step 1: Force Exact Match

Always explicitly declare the fourth argument as FALSE (or 0).
Change:
=HLOOKUP(B1, A2:M10, 3)
To:
=HLOOKUP(B1, A2:M10, 3, FALSE)
If this resolves the #N/A, the failure was caused by unsorted header rows in an approximate match scan.

Step 2: Audit Table Array Start Row

Inspect the first row of your range.
If your data table looks like this:

• Row 1: “Q1 Financial Summary” (Title block)
• Row 2: “Jan”, “Feb”, “Mar”, “Apr” (Column headers / Lookup Keys)
• Row 3: Revenue figures

A formula written as =HLOOKUP("Feb", A1:D3, 3, FALSE) will return #N/A because Excel looks for "Feb" in Row 1.
Fix: Adjust the range to start at Row 2: =HLOOKUP("Feb", A2:D3, 2, FALSE). Note that reducing the top row boundary also requires adjusting row_index_num downward by 1.

Step 3: What If the Lookup Value Still Fails to Match?

If the range starts on the correct row and exact match is enabled, verify that Excel recognizes the values as identical:

1. In an empty cell, enter =B1=A2 (replacing with your lookup cell and the target cell in the header row).
2. If this returns FALSE, an invisible difference exists.
3. Check for trailing spaces or hidden characters. To strip hidden characters, use the diagnostic technique covered in #N/A because of Hidden Non-Printing Characters (CLEAN function fix).

Closing

When resolving #N/A in HLOOKUP, focus your audit on the top row of the table array and verify that range_lookup is set to FALSE. If you find yourself frequently adjusting row index counts to avoid breaks, consider migrating legacy horizontal tables to modern vertical structures or adopting dynamic alternatives like INDEX/MATCH.