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:

Account ID	Account Name
0013600000LeWaJ	Caroline
0013600000LeWaj	Mike
0013600000LeWaK	Pat
0013600000LeWak	David
0013600000LeWaL	Emily
0013600000LeWal	Jeff
0013600000LeWaM	Kevin
0013600000LeWam	Greg
0013600000LeWaN	Jennifer
0013600000LeWan	Stephanie
0013600000LeWaO	Brad
0013600000LeWao	Tony

<tbody>
</tbody>

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.

Account ID	Account Name
0013600000LeWaJ	Caroline
0013600000LeWaj	Mike
0013600000LeWaK	Pat
0013600000LeWak	David
0013600000LeWaL	Emily
0013600000LeWal	Jeff
0013600000LeWaM	Kevin
0013600000LeWam	Greg
0013600000LeWaN	Jennifer
0013600000LeWan	Stephanie
0013600000LeWaO	Brad
0013600000LeWao	Tony

<tbody>
</tbody>

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