in excel vlookup 0 = exact match or approximate match
- User
in excel vlookup 0 = exact match or approximate match
- 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:
A B 1 Apple 2 Banana 3 Cherry 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) usingFALSE, it would return an error, while withTRUEit would return the next largest value if sorted.Always remember to use FALSE for an exact match when precision is critical.
-