• $ cat "

    Quick Tip: Excel VLOOKUP When Formatting Differs

    "

    Basic usage

    The Excel VLOOKUP formula is incredibly useful, but a bit finicky to get right if you do not use it often enough.

    The basic formula follows this format:=VLOOKUP(A1;Sheet2!A:B;2;FALSE)

    Parameters:

    1. The lookup value
    2. The range to use, in which the lookup column must be the first column of the range
    3. The column index in the lookup range where the actual value to use is found. Remember that the lookup column is counted as 1, so the next column to the right is column 2, and so on.
    4. Range lookup (Exact or approximate match). Use FALSE for an exact match.


    Important points to remember:

    • It is possible to specify the lookup range in the format A:F, which means that the entire columns are included in the range. I.e. you do not have to bother with specifying row numbers unless you actually need it.
    • If you do specify a range with line numbers, remember that you probably want to anchor the range with A$1:F$100 or $A$1:$F$100, so that you can drag copy the formula to a range without offsetting the lookup range


    Differences In Formatting Between Lookup Value and Lookup Column

    If the formatting differs between the lookup value and the lookup column, for example if one is a number formatted as a string and the other is formatted as a number, you will get an #N/A error in the VLOOKUP.

    If the lookup value is a number and the lookup column is text, you can use the TEXT function to fix the lookup: =VLOOKUP(TEXT(A1;"0");Sheet2!A:C;2;FALSE)

    If the situation is reversed, use the NUMBERVALUE function instead: =VLOOKUP(NUMBERVALUE(A1);Sheet2!A:C;2;FALSE)

    REF Errors

    If you end up getting a #REF error, this probably means that your lookup value was found in the lookup column, but the specified column number points to a column that is outside of the actual range. Let's say that you specify the range A:B but then you use the column number 3, which would actually point to the C column. Since the C column is not included in the range, Excel will give you a #REF error.

    If the lookup value was not found, the error would be #N/A instead.