I wanted to make my full-of-vlookups workbook faster so I thought I would start using vlookups(TRUE) on a sorted table since I read that its faster that way.
Problem is that, despite the fact that the lookup table is sorted and the fact that the lookup value does exist in said table, I can't get the vlookup(TRUE) to get the correct value. It does bring correct values on some lookups but not on all.
from what I read and considering that the lookup table is sorted, TRUE returns the wrong result only if an exact match is not found. Are you saying that the above claim is wrong?
As for FALSE being of help, I'm not sure what you mean, it's obvious from the original post that I'm already using FALSE.
Having the very same issue using TRUE I have used FALSE to get an "exact" match for each row, but the results are #NA. I think this might be a formatting issue for the data sheet. Or maybe the problem with the Page ID number.
I was starting to believe that I'm the only one having that issue 🙂
One year later and I still haven't found a solution to this.. I also thought about the 'formatting issue' you mentioned but I'm not sure what you mean about the page id number.. what is that id and how it can affect vlookup?
You are having the same issue that many people are experiencing. The answer should be simple, but it is NOT. I have a few people looking at this issue and we hope to discover a solution!
I only mentioned the page id because Microsoft Excel VLOOKUP Refersher mentions getting the name correct; which of course I have done. Agree the name ID is not the problem! I was just looking for a solution.
Hopefully someone with reply and tell us the issue with our VLOOKUP formula!
Hi. It might be that the item you are looking up is in the data multiple times. In which cases the vlookup will return the value for the first instance it finds.
In my case the vlookup is being done on a power query table which has any duplicate entries removed.
Just to be clear, the table where the vlookup is being done 'from' is not sorted the table that the vlookup looks 'to' is sorted with unique values in each cell.
The item for the look up is a part number. There are no duplicates. Some are similar (ie, 3M712 & 3M712-002CZ.
Using TRUE brings closest matches for all rows. But then changing to FALSE, get all rows #N/A. I need exact matches and there are exact matches in the full data sheet.
For my project, I was looking for exact matches; therefore FALSE would show correct data! In order to correct my issue needed to add the wild card asterisk to eliminate "hidden" leading or trailing spaces or characters in my part number column.
No wonder this question has been unanswered for so long 🙂
Here is the logic behind my question: when using TRUE and considering that the data we look for will *always* be in the table where we look to, I assumed that, instead for an approximation, vlookup will always find the exact match. eg. data to look for: 'example' table where we do the lookup contains the following data: data1 data2 example <--exact match example1 example2 example3
considering that the data table where we do the lookup will always contain the exact match text 'example', it is logical to assume that the lookup will choose said exact match instead eg. 'example2' or 'data1'
Based on that logic and knowing that the 'true' lookup is much faster than the 'false' lookup, I wanted to speed things up on my very large tables by using sorted data and a 'TRUE' vlookup but, unfortunately, it didn't work... my question is, 'why'?
'Index' combined with 'match' works faster than a 'FALSE' vlookup and brings the correct results no matter if the table is sorted or not.. why a 'TRUE' vlookup with a sorted table has trouble finding the correct match? Approximate or not it should find the match when on a sorted table and when the matched value is there.
