Forum Discussion

Franziska26's avatar
Franziska26
Copper Contributor
Mar 17, 2025

HLOOKUP transmits wrong value

I'm using HLOOKUP - WVERWEIS in German - to check a small series of values in Excel. I wrote the function once and used it via drag down for several cells. After transmitting the relevant numbers from one table to the target table there is one wrong value. It's the 13th of 15 in total. The origin table doesn't contain any functions or formulas itself, it's a copy of a table from an other worksheet. So I think I can rule out the source for this mistake being in the origin table for HLOOKUP. I can copy the single cell with which the mistake occurs and paste it into any other cell and the mistake won't happen; the value is copied correctly. I can also write the function HLOOKUP completely new for this single cell but the mistake still will happen. I cannot find any hint why it happens - any ideas how I can find out and/or solve that?

community

2 Replies

Sort By
  • Jigna's avatar
    Jigna
    Copper Contributor
    Mar 18, 2025

    Hi Franziska

    Can u send a screenshot of the excel sheet (Target Table ) which shows the wrong value...??

  • belzic's avatar
    belzic
    MCT
    Mar 18, 2025

    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))

Resources