#1
|
|||
|
|||
VLook Up Changing the Character
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, |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
Work Example
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 |
#4
|
|||
|
|||
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 |
#5
|
||||
|
||||
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))) |
#6
|
||||
|
||||
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). |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to mix character sizes without changing line spacing? | buttonsrtoys | Word | 3 | 03-29-2019 01:26 PM |
Vlook up multiple sequence | Cryken | Excel | 4 | 01-31-2017 08:45 AM |
Detecting that previous character doesn't exist (i.e., present character is first in document) | Robert K S | Word VBA | 15 | 08-01-2016 09:33 AM |
How can select from a specific character to another character | mohsen.amiri | Word | 2 | 02-19-2015 11:38 PM |
VLook up search | Tralee6 | Excel | 4 | 08-07-2014 05:20 AM |