Home

TRUE vlookup brings the wrong results

Sotiris Moschou
Occasional Contributor

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

13 Replies

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.

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!

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!

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.

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

 

 

 

 

 

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies