Case Sensitive vlookup (Entire List)

[ChoDuffield]

I need to do a case sensitive vlookup. I found several results here which list a formula similar to this:

=IF(EXACT(G4,VLOOKUP(G4,$C$4:$D$9,1,FALSE))=TRUE,VLOOKUP(G4,$C$4:$D$9,2,FALSE),"No exact match")

The problem is that the formula returns "No exact match" once it finds a match without the same case. I need the formula to continue looking for the match in the entire list. Here is what I'm currently getting back:

Data1	Data2		vlookup	result
aaa	a-small		bbb	No exact match
AAA	A-big		aaa	a-small
AaA	A-mixed		bBb	B-mixed
bBb	B-mixed		AaA	No exact match
bbb	b-small		BBB	No exact match
BBB	B-Big		AAA	No exact match

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

As you can see, 'bbb' is coming back with 'No exact match' when in fact there is one. It says that because bBb is listed first in the data. Compare this against 'aaa' which comes back with the correct match, but that value was listed first.

Does anyone know of a formula which will evaluate the entire list without stopping when incorrect casing is found?

Thank you!

 

[Teethless mama]

Excel Workbook
	A	B	C	D	E
1	Data1	Data2		vlookup	result
2	aaa	a-small		bbb	b-small
3	AAA	A-big		aaa	a-small
4	AaA	A-mixed		bBb	B-mixed
5	bBb	B-mixed		AaA	A-mixed
6	bbb	b-small		BBB	B-Big
7	BBB	B-Big		AAA	A-big
Sheet1

 

[Marcelo Branco]

Maybe

Formula in E2
=INDEX($B$1:$B$100,MATCH(TRUE,INDEX(EXACT($A$1:$A$100,$D2),0),0))
copy down

Assumes lookup values in column D

M.

 

[Aladin Akyurek]

I need to do a case sensitive vlookup. I found several results here which list a formula similar to this:

=IF(EXACT(G4,VLOOKUP(G4,$C$4:$D$9,1,FALSE))=TRUE,VLOOKUP(G4,$C$4:$D$9,2,FALSE),"No exact match")

The problem is that the formula returns "No exact match" once it finds a match without the same case. I need the formula to continue looking for the match in the entire list. Here is what I'm currently getting back:

Data1	Data2		vlookup	result
aaa	a-small		bbb	No exact match
AAA	A-big		aaa	a-small
AaA	A-mixed		bBb	B-mixed
bBb	B-mixed		AaA	No exact match
bbb	b-small		BBB	No exact match
BBB	B-Big		AAA	No exact match

<tbody>
</tbody>

As you can see, 'bbb' is coming back with 'No exact match' when in fact there is one. It says that because bBb is listed first in the data. Compare this against 'aaa' which comes back with the correct match, but that value was listed first.

Does anyone know of a formula which will evaluate the entire list without stopping when incorrect casing is found?

Thank you!

Also, possibly...

E2, just enter and copy down:

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

 

[ChoDuffield]

Thanks all. I had to do a little research to under the 1st two formulas, but I think I have it. The last one I'm not as clear on, but at this point I'm okay with it.

 

[Aladin Akyurek]

Thanks all. I had to do a little research to under the 1st two formulas, but I think I have it. The last one I'm not as clear on, but at this point I'm okay with it.

FIND is case sensitive. In the formula the FIND bit generates an array when it searches the items in A2:A7 in D2 one by one. The generated array consists of position numbers (integers). LOOKUP returns the value from B2:B7 whose position cooresponds to the position of the last numeric (integer) value of A2:A7.

1. LOOKUP(9.99999999999999E+307,FIND($A$2:$A$7,$D2),$B$2:$B$7)

2. LOOKUP(9.99999999999999E+307,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!},$B$2:$B$7)

3. LOOKUP(9.99999999999999E+307,
      {#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!},
      {"a-small";"A-big";"A-mixed";"B-mixed";"b-small";"B-Big"})

4. b-small

See the following links on how LOOKUP gets the last numeric value...

http://www.mrexcel.com/forum/showthread.php?102091
http://www.mrexcel.com/forum/showthread.php?310278 (post #7)