HLOOKUP transmits wrong value

1. Check for Hidden Formatting or Characters
Even though the origin table appears fine, there could be hidden characters, leading/trailing spaces, or formatting issues in the problematic cell.

Use the TRIM function to clean up any spaces: =TRIM(A1) (replace A1 with the relevant cell).

Check for non-visible characters by using LEN to count the number of characters: =LEN(A1). If the length seems off, there might be hidden characters.

2. Ensure Exact Match (FALSE) Is Used in HLOOKUP
If your HLOOKUP formula is set to approximate match (i.e., the 4th argument is TRUE or omitted), it could pick an incorrect value, especially if the lookup array isn't sorted properly.

Check the formula and ensure you use FALSE as the 4th argument for an exact match:
=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

3. Inspect the Lookup Value
The value you’re searching for in the 13th row might have slight differences compared to its counterparts in other rows:

Compare the lookup value using EXACT:
=EXACT(A1, B1)
This will return TRUE if the two cells are an exact match, even in terms of case sensitivity.

4. Verify Table Array Consistency
If your source table is copied from another worksheet, it might inadvertently include mismatched formatting or merged cells.

Ensure the table_array referenced in the HLOOKUP formula contains only plain, non-merged cells.

Double-check that the lookup range covers all the relevant rows and columns.

5. Look for Phantom Errors
If the HLOOKUP is working for all other rows but failing for one, there might be a phantom error in your formula logic. Test the cell for errors:

Use ISERROR or ISNA:
=ISERROR(HLOOKUP(...))
This can help pinpoint if the error lies in the HLOOKUP function.

6. Rebuild the Formula and Test in Isolation
Sometimes a formula becomes corrupted or misaligned during copy-pasting. Test the following:

Manually rewrite the formula for the 13th row in a completely separate part of the worksheet (not part of the existing range).

If this works, the issue might be related to how the formulas were copied or dragged down.

7. Debug Using Step-by-Step Evaluation
Excel provides a way to evaluate each part of your formula to check where it might fail:

Select the cell with the HLOOKUP formula.

Go to Formulas > Evaluate Formula.

Step through the formula to see if any part evaluates unexpectedly.

8. Use Index/Match as an Alternative
If the issue persists and can't be resolved, consider replacing HLOOKUP with INDEX and MATCH. These functions offer more flexibility and often avoid quirks found in HLOOKUP:
=INDEX(row_array, MATCH(lookup_value, column_array, 0))