SearchV without sort
Solved
Anonymous user
-
Anonymous user -
Anonymous user -
Hello,
I am intrigued by this question. When I was taught the VLOOKUP function, I was told that the data in the first column must be absolutely sorted. Also, I have seen some questions on forums saying they can't use the function without the values being sorted.
However, if you specify "FALSE" for the last argument of the function, there is no need to sort the list. In fact, Excel's help emphasizes this:
"If the approximate_match argument is FALSE, it is not necessary for the values in the first column of the table_array to be sorted."
So, I would like to know if I can trust this and use the function without my list being sorted. Have you ever had issues? So far, I haven't had any problems; the displayed values are correct. However, I don't want to run into a bug later, especially with a client's file.
Thank you for reassuring me :)
Configuration: Windows 7 / Chrome 31.0.1650.63
I am intrigued by this question. When I was taught the VLOOKUP function, I was told that the data in the first column must be absolutely sorted. Also, I have seen some questions on forums saying they can't use the function without the values being sorted.
However, if you specify "FALSE" for the last argument of the function, there is no need to sort the list. In fact, Excel's help emphasizes this:
"If the approximate_match argument is FALSE, it is not necessary for the values in the first column of the table_array to be sorted."
So, I would like to know if I can trust this and use the function without my list being sorted. Have you ever had issues? So far, I haven't had any problems; the displayed values are correct. However, I don't want to run into a bug later, especially with a client's file.
Thank you for reassuring me :)
Configuration: Windows 7 / Chrome 31.0.1650.63
4 answers
-
Hello.
I use this function daily with the false argument on unsorted data, and there has never been an error. -
Hello,
For me, the data should not be sorted.
I think the issue of sorting arises if your sought value appears multiple times.
I have already used it on unsorted lists and it works very well. -
Hello,
VLOOKUP(code;table;ResultColumn;True or False)
If the value being searched is a code and if the table is NOT sorted
You must specify the FALSE parameter.
You will get #N/A if the code does not exist.
If the value being searched is a code and if the table is SORTED,
you can specify the TRUE parameter.
The search is then done by BINARY SEARCH and can be x100 + FASTER since it only takes a few accesses to find the code. This is VERY IMPORTANT when the table is large and the
VLOOKUP() formula is copied x1000 times (With FALSE, Excel consults the table SEQUENTIALLY).
To check if the code exists (you do not get #N/A but the lower value), you must write:
=IF(VLOOKUP(LookedUpCode;Articles;1;TRUE)=
LookedUpCode;VLOOKUP(LookedUpCode;Articles;2;TRUE);"Unknown")
If the table is sorted by code and the code exists multiple times, VLOOKUP(code;table;Result_Column;TRUE) positions on the last code.
Jacques Boisgontier -
>So if I understand correctly, if I use VLOOKUP with an unsorted table with several values (like 20,000), it could be slow?
If there's only one VLOOKUP formula, no problem.
However, if we copy this formula 1,000 times, there will be a wait time.
In this case, use:
Custom function RechvM() matrix faster than classic VLOOKUP()
If we change the 2,600 values looked up in a table of 20,000 items,
the recalculation time is not visually measurable (5 sec for VLOOKUP())
-Select G2:G2673
=RechvM(F2:F2673;mytable;2)
-Confirm with shift+ctrl+enter
In a module
Alt+F11/Insert module
Function RechvM(key As Range, field As Range, colResult)
Application.Volatile
Set d = CreateObject("Scripting.Dictionary")
a = field.Value
b = key.Value
For i = LBound(a) To UBound(a)
d(a(i, 1)) = a(i, colResult)
Next i
Dim temp()
ReDim temp(LBound(b) To UBound(b))
For i = LBound(b) To UBound(b)
temp(i) = d(b(i, 1))
Next i
RechvM = Application.Transpose(temp)
End Function
Jacques Boisgontier