HLOOKUP stops working in Google Sheets when lookup values aren’t found, ranges are incorrect, match settings are wrong, or data formatting breaks the lookup logic.
This usually appears as #N/A, wrong results, blanks, or inconsistent matches.
Why is HLOOKUP not working in Google Sheets?
Common causes:
- Wrong match type (
TRUEvsFALSE) - Lookup value missing
- Incorrect lookup range
- Hidden spaces in data
- Text vs number mismatch
- Wrong row index number
- Horizontal table structured incorrectly
- Approximate matching errors
What does HLOOKUP do in Google Sheets?
HLOOKUP searches horizontally across the first row of a table and returns a value from another row.
Example:
=HLOOKUP(A2,B1:F3,2,FALSE)
Meaning:
- Find
A2in first row ofB1:F3 - Return value from row 2
- Use exact match
Unlike VLOOKUP, which searches vertically, HLOOKUP searches left to right.
Why am I getting #N/A in HLOOKUP?
Most common cause:
- Value not found
Example:
=HLOOKUP(A2,B1:F3,2,FALSE)
If A2 does not exist in row 1:
Result:
#N/A
Fix:
- Verify lookup value exists
- Remove hidden spaces
Clean lookup value:
=TRIM(CLEAN(A2))
Why is HLOOKUP returning the wrong value?
Usually caused by approximate matching.
Wrong:
=HLOOKUP(A2,B1:F3,2)
Without the last argument:
- Google assumes approximate match (
TRUE)
Better:
=HLOOKUP(A2,B1:F3,2,FALSE)
Always use:
FALSE = exact match
for reliable results.
Why is HLOOKUP returning the wrong row?
Problem:
- Incorrect row index
Example:
Data:
Product | Price | Stock
Wrong:
=HLOOKUP(A2,B1:F3,5,FALSE)
If table only has 3 rows:
- Error occurs
Fix:
- Verify row number exists inside range.
Why is HLOOKUP not finding text values?
Common causes:
- Hidden spaces
- Case inconsistencies
- Text formatting mismatch
Fix:
=TRIM(CLEAN(A2))
Normalize source:
=LOWER(A2)
Apply same cleaning to lookup row.
Why are numbers not matching in HLOOKUP?
Problem:
Numbers stored as text.
Example:
"100"
instead of:
100
Fix:
=VALUE(A2)
Then retry lookup.
Why is HLOOKUP returning blank?
Possible causes:
- Source cell blank
- Formula wrapped inside
IF() - Wrong row selected
Example:
=IF(HLOOKUP(A2,B1:F3,2,FALSE)="","No Data","Found")
Check actual lookup output separately.
Why is HLOOKUP failing after copying the formula?
Problem:
Relative references changing.
Wrong:
=HLOOKUP(A2,B1:F3,2,FALSE)
Dragged sideways:
- Range shifts
Better:
=HLOOKUP(A2,$B$1:$F$3,2,FALSE)
Use absolute references.
Why is HLOOKUP slow on large sheets?
Common cause:
Large ranges.
Bad:
=HLOOKUP(A2,B:Z,2,FALSE)
Better:
=HLOOKUP(A2,B1:Z5000,2,FALSE)
Limit lookup size.
Should I use HLOOKUP or INDEX MATCH?
For simple horizontal lookup:
Use:
HLOOKUP
For flexibility:
Use:
INDEX MATCH
Example:
=INDEX(B2:F2,MATCH(A2,B1:F1,0))
More flexible and easier to scale.
HLOOKUP vs alternatives
| Formula | Best Use |
|---|---|
| HLOOKUP | Horizontal lookup |
| VLOOKUP | Vertical lookup |
| INDEX MATCH | Flexible lookups |
| XLOOKUP | Cleaner modern lookup |
Best practices for HLOOKUP
- Always use
FALSEfor exact matching - Clean lookup values before matching
- Use absolute references
- Limit lookup ranges
- Verify row indexes carefully
- Avoid approximate matching unless required
FAQs
Why is HLOOKUP returning #N/A?
The lookup value is missing or formatted differently.
Why is HLOOKUP returning wrong results?
Approximate matching (TRUE) is usually the cause.
Should I use FALSE in HLOOKUP?
Yes. Exact match is more reliable.
Why is HLOOKUP not finding numbers?
Numbers may be stored as text. Convert using VALUE().
Why does HLOOKUP break when copied?
The range reference shifts. Use $ absolute references.
What is better than HLOOKUP?
INDEX MATCH or XLOOKUP are often more flexible and easier to scale.
Other Google Sheets Fixes: