EXCEL: Is it *possible* to do a case-sensitive lookup?

[MRyerson]

This is a piece of the table I am looking up in:
CODE Description
2A Pocket 2Panel/1Track-2P1
2B Pocket 2Panel/2Track-2P2
2b ByPass 2Panel/2Track-2BY2
3b ByPass 3Panel/2Track-3CO2
3c ByPass 3Panel/3Track-3BY3
3C Pocket 3Panel/3Track-3P3

VLOOKUP doesn't work, because it is not case-sensitive. Is there another way to do this? Even VBA?

M

 

[xlbo]

you could test the result using the EXACT function eg:

=if(EXACT(Vlookup(A2,myRange,2,false),A2),Vlookup(A2,myRange,2,false),"No Match")

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Yes, you could construct a formula to do this using SUMPRODUCT, like this:

=INDEX(B2:B20,SUMPRODUCT((EXACT("2b",A2:A20)*(ROW(1:19)))))

note that the ROW function references a range going from row 1 for a number of rows equivalent to the number of rows in the range being tested, this gives the index number into the range to be retrieved.

I hope I made it clear.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[bkpchs237]

MRyerson,
The only case-sensitive function I could think of to use in this instance was the Find function. Place it in an array-entered formula below to get your answer:

=INDIRECT("C"&SUM(IF(ISERROR(FIND($A2,$A$2:$A$9,1)),0,1)*ROW($A$2:$A$9)))

Hope this helps.

 

[MRyerson]

Thanks to all of you for your suggestions.

Here is what I did:

First, I put this formula in the column next to the codes (they are all 2-character codes) I wanted to look up, in a range named "MyList"

=256*(CODE(LEFT(C4)))+CODE(RIGHT(C4))

Then, on the page where I wanted to display the descriptions, I put this:

=VLOOKUP(256*(CODE(LEFT(D2)))+CODE(RIGHT(D2)),MyList,2,FALSE)

Perhaps not as elegant, but it works.

Marty