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...
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.
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.
