Dec 14 2017 12:10 AM
Dec 14 2017 12:10 AM
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?
Dec 14 2017 01:30 AM
TRUE is used for an approximate match FALSE is used for exact match in "VLOOKUP" formula. Using false should help.
Dec 14 2017 01:36 AM - edited Dec 14 2017 01:36 AM
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.
Jan 05 2019 03:17 PM
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!
Jan 05 2019 03:31 PM
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?
Jan 05 2019 04:03 PM
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!
Jan 05 2019 09:03 PM
Jan 05 2019 09:16 PM
Jan 06 2019 11:26 AM
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.
Jan 07 2019 03:28 PM
Found the solution!!! To be honest, my sister did! Teamwork is great!
This formula fixed the issue.
Hope this helps others at the Microsoft board.
Jan 07 2019 04:30 PM
Updating by taking out the space before the *.
Let me know if this helps with your VLOOKUP.
Jan 07 2019 08:12 PM - edited Jan 07 2019 08:13 PM
Thanks for that
Before I test it though,
doesn't the fact that you are using 'FALSE' beat the original purpose of using 'TRUE'?
I mean the reason I chose 'TRUE' (as it's also shown on the original post) was to speed things up but it didn't work (ie. I got the wrong results).
Are you saying that by adding an asterisk to a 'FALSE' vlookup speeds things up like when using 'TRUE'?
I'm obviously missing something here :)
Jan 08 2019 02:58 PM
Maybe I misunderstood your goal using TRUE. You mention "speed things up"; not sure what you mean by that. Were you looking for approximate matches?
Using TRUE gives you approximate matches . Using FALSE gives you exact matches.
(see link below for a detailed explanation from Microsoft)
Quick Reference Card by Microsoft. https://support.office.com/en-us/article/quick-reference-card-vlookup-refresher-750fe2ed-a872-436f-9...
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.
Jan 08 2019 03:49 PM