Is there a case sensitive VLOOKUP?

[Analytics]

I have a unique identifier (Contact ID). It however is case sensitive. Therefore, if I vlookup 0033000000KU7O5 (should be Bob) it will find first Amy.

Amy Brown: 0033000000kU7O5
Bob Smith: 0033000000KU7O5
Chris Waters: 003a000001jWWRh

I want to vlookup based upon this ID however a vlookup is not case sensitive. Does anyone know what formula is?

Thx!

 

[Analytics]

shyy. you're formula only works if they IDs are in the same order. If you invert 1a and 1A in column A you will find that the formula doesn't function correctly.

 

[Analytics]

Aladin Akyurek : Your formula does the trick! Thanks so much!!! Can you explain to how it works?

 

[Aladin Akyurek]

Sheet1, C2, just enter:

=LOOKUP(9.99999999999999E+307,FIND($A2,Sheet2!$A$2:$A$20),
  Sheet2!$B$2:$B$20)

Aladin Akyurek : Your formula does the trick! Thanks so much!!!

You're welcome.

Can you explain to how it works?

[1]

LOOKUP(9.99999999999999E+307,Reference)

picks up the last numeric value from Reference if any. The big number, used as look up value, is an Excel constant, which can be found under "limits" in Excel's help. Note that LOOKUP ignores error values of Reference if it possibly can.

[2]

LOOKUP(9.99999999999999E+307,MatchReference,ResultReference)

returns the value from ResultReference that is at the same position as the last numeric value that it comes across in MatchReference. Put otherwise, LOOKUP here correlates the last numeric value with a result value.

[3] The LOOKUP formula we have invoked is structured the same as the formula in [2]...

LOOKUP(9.99999999999999E+307,FindReference,ResultReference)

where FindReference is itself an expression that invokes FIND and ResultReference an unmodified range:

FIND($A2,Sheet2!$A$2:$A$20)

Sheet2!$B$2:$B$20

LOOKUP thus correlates the cell that houses A2 in Sheet2!$A$2:$A$20 with a cooresponding cell Sheet2!$B$2:$B$20. Note that FIND returns position numbers if successful, otherwise #VALUE! errors.

Pictured with concrete/example values:

LOOKUP(9.99999999999999E+307,{2;#VALUE!;7;...},{"Kad";"Nad";"Vad";...}

The result is: Vad.

Hope this helps.