VLOOKUP with case sensitive

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James - 08 Apr 2004 13:20 GMT

Hello,

I'm trying to run a VLookup with account IDs, the problem is I may have two
that are the same but in different cases :

00100000002s74J
00100000002S74j

Is it possible to distinguish between these two in the Vlookup?

Thanks very much for your help,

James

Reply to this Message

Frank Kabel - 08 Apr 2004 13:33 GMT

Hi James
try the following array formula (enetred with CTRL+SHIFT+ENTER):
=INDEX(B1:B100,MATCH(TRUE,EXACT(A1:A100,"00100000002s74J"),0))

to return the value from column B

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> Hello,
>
[quoted text clipped - 9 lines]
>
> James

Reply to this Message

James - 09 Apr 2004 09:23 GMT

Hi Frank,

thanks very much for your reply. The problem is I can have many account IDs
that are the same but are case sensitive. Is it possible to concatenate
VLOOKUP with EXACT for example?

Thanks again,

James

> Hi James
> try the following array formula (enetred with CTRL+SHIFT+ENTER):
[quoted text clipped - 21 lines]
> >
> > James

Reply to this Message

Frank Kabel - 09 Apr 2004 09:32 GMT

Hi James
the formula below should do this (I replaced VLOOKUP with a combination
of MATCH and INDEX). This formula is case sensitive. Of course you can
replace the hard-coded lookup criteria "00100000002s74J" with a cell
reference.
Assumption I made:
- column A contains the account IDs.
- column B the value you want returned

So did you try the formula or do you encounter problems with that
approach? And if yes which problems?
You may also explain your spreadsheet layout a little bit more to get a
more custom tailored answer :-)

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> Hi Frank,
>
[quoted text clipped - 31 lines]
>>>
>>> James

Reply to this Message