How to make a lookup Case Sensitive

[etemkin56]

Hi,

I am working on combining a list based on an unique Account ID's. But the formula needs to be case sensitive. I have tried the following formula but continue to get an error no matter what I do. I have never used an index formula before so this might be an easy fix that I am just not seeing. Below is an example of the account IDs that I need to differentiate between.

=INDEX(AccountID,MATCH(TRUE,EXACT(D3,A1:A3008),2))

Account ID is the named range containing two columns:
[TABLE="width: 264"]
<tbody>[TR]
[TD]Account ID[/TD]
[TD]Account Name[/TD]
[/TR]
[TR]
[TD]0013600000LeWaJ[/TD]
[TD]Caroline[/TD]
[/TR]
[TR]
[TD]0013600000LeWaj[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]0013600000LeWaK[/TD]
[TD]Pat[/TD]
[/TR]
[TR]
[TD]0013600000LeWak[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]0013600000LeWaL[/TD]
[TD]Emily[/TD]
[/TR]
[TR]
[TD]0013600000LeWal[/TD]
[TD]Jeff[/TD]
[/TR]
[TR]
[TD]0013600000LeWaM[/TD]
[TD]Kevin[/TD]
[/TR]
[TR]
[TD]0013600000LeWam[/TD]
[TD]Greg[/TD]
[/TR]
[TR]
[TD]0013600000LeWaN[/TD]
[TD]Jennifer[/TD]
[/TR]
[TR]
[TD]0013600000LeWan[/TD]
[TD]Stephanie[/TD]
[/TR]
[TR]
[TD]0013600000LeWaO[/TD]
[TD]Brad[/TD]
[/TR]
[TR]
[TD]0013600000LeWao[/TD]
[TD]Tony[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be very appreciated it!!
Thanks in advance!
Emily

 

[AliGW]

What do you mean exactly by "the formula needs to be case sensitive"? Please list some expected outcomes and how the formula is failing.

 

[Aladin Akyurek]

Let A1:B13 house the table of interest, the headers included.

In E2 enter and copy down:

=LOOKUP(9.99999999999999E+307,FIND(D2,$A$2:$A$13),$B$2:$B$13)

where D2 houses a value like:
<strike></strike>
0013600000LeWaL<strike></strike>

 

[etemkin56]

What do you mean exactly by "the formula needs to be case sensitive"? Please list some expected outcomes and how the formula is failing.

In the example ID's I showed some of them are exactly the same except for the upper/lower case letters. So I need some way to distinguish that 0013600000LeWaJ does not equal 0013600000LeWaj and so on. In the below example, when I am looking for 0013600000LeWaj I want the returned value to equal Mike and not Caroline. When I use a standard vlookup, it will return Caroline because the vlookup doesn't recognize the difference in upper/lower case.


[TABLE="width: 264"]
<tbody>[TR]
[TD]Account ID[/TD]
[TD]Account Name[/TD]
[/TR]
[TR]
[TD]0013600000LeWaJ[/TD]
[TD]Caroline[/TD]
[/TR]
[TR]
[TD]0013600000LeWaj[/TD]
[TD]Mike[/TD]
[/TR]
[TR]
[TD]0013600000LeWaK[/TD]
[TD]Pat[/TD]
[/TR]
[TR]
[TD]0013600000LeWak[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]0013600000LeWaL[/TD]
[TD]Emily[/TD]
[/TR]
[TR]
[TD]0013600000LeWal[/TD]
[TD]Jeff[/TD]
[/TR]
[TR]
[TD]0013600000LeWaM[/TD]
[TD]Kevin[/TD]
[/TR]
[TR]
[TD]0013600000LeWam[/TD]
[TD]Greg[/TD]
[/TR]
[TR]
[TD]0013600000LeWaN[/TD]
[TD]Jennifer[/TD]
[/TR]
[TR]
[TD]0013600000LeWan[/TD]
[TD]Stephanie[/TD]
[/TR]
[TR]
[TD]0013600000LeWaO[/TD]
[TD]Brad[/TD]
[/TR]
[TR]
[TD]0013600000LeWao[/TD]
[TD]Tony[/TD]
[/TR]
</tbody>[/TABLE]


I hope that better explains what I am trying to do...

 

[AliGW]

@Aladin - your formula returns 0 here.

EDIT: no, it DOES work as long as I have the headers!

 

[etemkin56]

Amazing! It worked, thank you so much!!

 

[Aladin Akyurek]

Amazing! It worked, thank you so much!!

You are welcome.

 

[AliGW]

Can you explain HOW it works?

 

[Aladin Akyurek]

Can you explain HOW it works?

See: http://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323