Menu
Why does this work, and looks up values correctly
but once i change the order of values, it produces incorrect values?
317
VLOOKUP Exact Match uses unsorted data in Excel. Based on how Excel works, VLookup doesn't need to be sorted, if the 4th parameter is FALSE. The 4th Parameter determines whether an approximate search is used or an exact match is used. by default true is used and an approximate search requires a sorted range.
If is_sorted is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned. If VLOOKUP doesn't appear to be giving correct results, check that the last argument is set to FALSE . If the data is sorted and you need to optimize for performance, set it to TRUE .
If an exact match is not found, the Lookup function will match the closest value below the lookup value.
If you read the notes on the LOOKUP function, it says:
The LOOKUP function will only work properly if data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP, or other related functions if data is not sorted.
Change your formula to use VLOOKUP as follows:
=VLOOKUP(D3, A1:B6, 2, FALSE)
Syntax:
VLOOKUP(search_key, range, index, [is_sorted])
search_key - The value to search for. For example, 42, "Cats", or I24.
range - The range to consider for the search. The first column in the range is searched for the key specified in search_key.
index - The column index of the value to be returned, where the first column in range is numbered 1.
is_sorted - [OPTIONAL - TRUE by default] - Indicates whether the column to be searched (the first column of the specified range) is sorted.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
