Forum Discussion
TRUE vlookup brings the wrong results
Hello to all,
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.
Any ideas why might that be happening?
thank you
- 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.
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.
- 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.
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.
=VLOOKUP(A2,costing!$A$2:$B$3000, 2, FALSE)
Appreciate any help!
Found the solution!!! To be honest, my sister did! Teamwork is great!
This formula fixed the issue.
=VLOOKUP(A2&" *",costing!$A:$B,2,FALSE)
Hope this helps others at the Microsoft board.
THANK YOU! I was using VLOOKUP to locate matching company names between sheets, but some of the names would include "Inc." etc. as the data was pulled from different sources. My first attempt only pulled exact matches, but adding the &"*" added the different versions of the company names. You are a life saver!
Thanks for the reply,
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!
TRUE is used for an approximate match FALSE is used for exact match in "VLOOKUP" formula. Using false should help.
Hello,
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.
