Hey All,




I am using the feature VLook Up to bring in Salesforce ID numbers to a spreadsheet but for some reason, the VLook Up function is ignoring the size of a letter that is lower case, instead of looking for the Uppercase letter.


Example:
On the Reference Sheet. (Used for the VLook Ups.

15 Character ID = 0061H00000iE0xB
18 Character ID = 0061H00000iE0xBQAS


When I do the VLook Up. I'm asking it to look at the 15 character ID on the load sheet and return me the 18 Character ID.


What is happening, is the VLook UP is stopping at the ID 0061H00000iE0xb and it brings back the 18 character ID for that record = 0061H00000iE0xbQAC


I don't understand why it has no problem with the lower case "i" or "x" in the 15 character ID but when it gets to the last character, which is an Upper Case "B", it takes the ID with the lower case "b" instead of looking further down the Reference sheet for the ID that has the Upper Case "B".


Sorry, I hope that makes sense.


Is there a setting or something I need to switch off or on?


Thank you,

Can you post an example worksheet so we can see what is happening and we dont need to create the data?


Are you putting false/0 at the end of the vlookup for an exact match?

Afternoon,


Thank you for getting back to me, much appreciated.
I always use "false" in the last box of the VLook Up, but what is "false/0"?


Here is an example of the issue...
I have Reference sheets from SFDC. The sheets have both the 15 character ID and the 18 character ID.
The 1st column is the 18 character ID.
The 2nd column is the 15 character ID.
The "case" of the letter B changed. The search results (the 18 character ID) should have been an Upper Case "B", instead I got the Lower Case "b", which is actually a different Opportunity in SFDC.


(See attachment below)



Basically, Excel is not looking for the EXACT match. It is ignoring Case, and stopping at the lower case "b" instead of searching further down the reference sheet for the ID with the Capital "B".


(See attachment below)


I hope that makes sense?

I just don't understand why Excel is ignoring letter Case (Capitals vs small).
My VLook Up setting look like this...



Thank you
Shawn Low

Attached Images

	Example #1.png (41.2 KB, 20 views)
	Example #2.png (32.1 KB, 20 views)

false/0 is the same thing you either put false or 0.


Can you attach a file and not a screen shot, i cant use a screen shot

Supposing tour range is A1:B100, try something like

=INDEX($B$1:$B$100,SUMPRODUCT(--ISNUMBER(FIND(your_lookup_value,$A$1:$A$100))*ROWS (A1:B100)))

VLOOKUP is case insensitive.
One work-around is to use EXACT, so instead of:
=VLOOKUP(A6,$F$2:$G$5,2,FALSE)
use:
=INDEX($G$2:$G$5,MATCH(TRUE,EXACT(A6,$F$2:$F$5)))
which should be committed to the sheet using Ctrl+Shift+Enter, not just Enter (not needed on Office 365 versions).