Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

in excel vlookup 0 = exact match or approximate match

  1. User

    in excel vlookup 0 = exact match or approximate match

  2. Assistant

    In Excel, when using the VLOOKUP function, the fourth argument (also known as the range_lookup argument) determines whether the lookup will be an exact match or an approximate match.

    • If you set the fourth argument to FALSE or 0, VLOOKUP will perform an exact match. This means VLOOKUP will look for an exact match of the lookup value in the first column of the specified range.

    • If you set the fourth argument to TRUE or omit it, VLOOKUP will perform an approximate match. In this case, the data in the first column of your range must be sorted in ascending order.

    Here’s a quick example of the syntax:

    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
    
    • lookup_value: The value you want to look up.
    • table_array: The range of cells that contains the data.
    • col_index_num: The column number in the table from which to retrieve the value.
    • [range_lookup]: Set to FALSE for an exact match or TRUE for an approximate match (or omit for an approximate match).

    Example

    Suppose you have the following data:

    AB
    1Apple
    2Banana
    3Cherry

    Using =VLOOKUP(2, A1:B3, 2, FALSE) would return "Banana" (exact match), while =VLOOKUP(2, A1:B3, 2, TRUE) would also return "Banana" if the first column values were sorted in ascending order. However, if you looked for a value not present (like 4) using FALSE, it would return an error, while with TRUE it would return the next largest value if sorted.

    Always remember to use FALSE for an exact match when precision is critical.