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!

 

[shg]

No without seeing the formula you're using now, but it will be an INDEX/MATCH using the EXACT function.

And it will be slow if the list is large.

 

[shyy]

Excel 2010
	A	B	C	D
1	Name	Age	joe	No exact match
2	Mary	32
3	Joe	48
4	Bob	53
5	Sue	27
Sheet1

Cell Formulas
Range	Formula
D1	=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(C1,A1:B5,2,FALSE),"No exact match")

 

[Analytics]

To make it simple...

Sheet 1
column A: Contact ID
Column B: Contact Name
Column C: Vlookup to pull over Birthdate

Sheet 2
Column A: Contact ID
Column B: Birthdate

I want to vlookup (using contact ID) the birthdate and pull that to sheet 1, column C.

Currently the vlookup won't work because it is not case sensitive. It would put Amy's DOB rather than BOB's. I know there is a way. I have done it before but unfortunately forgotten how.

Thx

 

[Analytics]

shyy: The formula didn't work for me....

Try changing joe to Joe (should find a match). It still says "No exact match".

 

[shg]

Could you POST the formula you're using that doesn't work?

 

[Analytics]

=IF(EXACT(A1,VLOOKUP(A1,E:F,1,FALSE))=TRUE,VLOOKUP(A1,E:F,2,FALSE),"No Exact Match")

1A	No Exact Match			1a	Bob
1a				1A	Amy
2a				2a	Chris

<tbody>
</tbody><colgroup><col><col><col span="4"></colgroup>

 

[Analytics]

Column A begins with 1A. Column B begins with No Exact Match. Column C and D are blank. Column E begins with 1a. Column F begins with Bob. I was trying to use shyy's formula

 

[shyy]

works for me

Excel 2010
	A	B	C	D	E	F
1	1a	Bob			1a	Bob
2	1A	Amy			1A	Amy
3					2a	Chris
Sheet2

Cell Formulas
Range	Formula
B1	=IF(EXACT(A1,VLOOKUP(A1,E1:F3,1,FALSE))=TRUE,VLOOKUP(A1,E1:F3,2,FALSE),"No exact match")
B2	=IF(EXACT(A2,VLOOKUP(A2,E2:F4,1,FALSE))=TRUE,VLOOKUP(A2,E2:F4,2,FALSE),"No exact match")

 

[Aladin Akyurek]

To make it simple...

Sheet 1
column A: Contact ID
Column B: Contact Name
Column C: Vlookup to pull over Birthdate

Sheet 2
Column A: Contact ID
Column B: Birthdate

I want to vlookup (using contact ID) the birthdate and pull that to sheet 1, column C.

Currently the vlookup won't work because it is not case sensitive. It would put Amy's DOB rather than BOB's. I know there is a way. I have done it before but unfortunately forgotten how.

Thx

Sheet1, C2, just enter:

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