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...
Forum Discussion
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.
Updating by taking out the space before the *.
=VLOOKUP(A2&"*",costing!$A:$B,2,FALSE)
Let me know if this helps with your VLOOKUP.
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 :)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-92aa-36c17e53f2ee
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.
I hope I made my question clearer this time.
thank you for this but am lost @costing. FALSE vlookup is giving me wrong results even though the values are there on the table.
