any way to make vlookup case sensitive?

I'm using vlookup for translating a code into a decode value. The codes are
in both lowercase and uppercase, unfortunately with the same values (e.g.,
cnv and CNV represent 2 different things). Anyone know how to make vlookup
case sensitive? Or is there another fancy excel function that can provide
the equivalent functionality?

Thanks!

One way (albeit replacing vlookup with another formula)

Assume the vlookup would look like this

=VLOOKUP(A1,C2:D50,2,0)

to lookup a case sensitive string from A1 in column C and return the values
from D would be

=INDEX(D2:D50,MATCH(1,--ISNUMBER(FIND(A1,C2:C50)),0))

entered with ctrl + shift & enter



--

Regards,

Peo Sjoblom



"Dan in NY" <[email protected]> wrote in message
news:[email protected]...
> I'm using vlookup for translating a code into a decode value. The codes
are
> in both lowercase and uppercase, unfortunately with the same values (e.g.,
> cnv and CNV represent 2 different things). Anyone know how to make
vlookup
> case sensitive? Or is there another fancy excel function that can provide
> the equivalent functionality?
>
> Thanks!

try this where the lookup value is in columnc and the value you need is in
col D
This is an ARRAY formula so needs to be entered with ctrl+shift+enter

=INDEX(D21:D100,MATCH(TRUE,EXACT(C21:C100,"C"),0))

--
Don Guillett
SalesAid Software
[email protected]
"Dan in NY" <[email protected]> wrote in message
news:[email protected]...
> I'm using vlookup for translating a code into a decode value. The codes
are
> in both lowercase and uppercase, unfortunately with the same values (e.g.,
> cnv and CNV represent 2 different things). Anyone know how to make
vlookup
> case sensitive? Or is there another fancy excel function that can provide
> the equivalent functionality?
>
> Thanks!

Getting closer, but the FIND pulls in an earlier lookup decode that simply
contains the searched for item. For example, if one of my lookup values is
"F", your solution returns the column B for "CFD" - I assume since it finds
the "F" in "CFD" before it gets to the "F" entry (the lookup decode list is
in alphabetical order). hmm...tricky.

Any more suggestions?

Thanks!

"Peo Sjoblom" wrote:

> One way (albeit replacing vlookup with another formula)
>
> Assume the vlookup would look like this
>
> =VLOOKUP(A1,C2:D50,2,0)
>
> to lookup a case sensitive string from A1 in column C and return the values
> from D would be
>
> =INDEX(D2:D50,MATCH(1,--ISNUMBER(FIND(A1,C2:C50)),0))
>
> entered with ctrl + shift & enter
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
> "Dan in NY" <[email protected]> wrote in message
> news:[email protected]...
> > I'm using vlookup for translating a code into a decode value. The codes
> are
> > in both lowercase and uppercase, unfortunately with the same values (e.g.,
> > cnv and CNV represent 2 different things). Anyone know how to make
> vlookup
> > case sensitive? Or is there another fancy excel function that can provide
> > the equivalent functionality?
> >
> > Thanks!
>
>
>

=INDEX(D2:D50,MATCH(1,--EXACT(C2:C50,A1),0))

also array entered

--

Regards,

Peo Sjoblom


"Dan in NY" <[email protected]> wrote in message
news:[email protected]...
> Getting closer, but the FIND pulls in an earlier lookup decode that simply
> contains the searched for item. For example, if one of my lookup values
is
> "F", your solution returns the column B for "CFD" - I assume since it
finds
> the "F" in "CFD" before it gets to the "F" entry (the lookup decode list
is
> in alphabetical order). hmm...tricky.
>
> Any more suggestions?
>
> Thanks!
>
> "Peo Sjoblom" wrote:
>
> > One way (albeit replacing vlookup with another formula)
> >
> > Assume the vlookup would look like this
> >
> > =VLOOKUP(A1,C2:D50,2,0)
> >
> > to lookup a case sensitive string from A1 in column C and return the
values
> > from D would be
> >
> > =INDEX(D2:D50,MATCH(1,--ISNUMBER(FIND(A1,C2:C50)),0))
> >
> > entered with ctrl + shift & enter
> >
> >
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> > "Dan in NY" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm using vlookup for translating a code into a decode value. The
codes
> > are
> > > in both lowercase and uppercase, unfortunately with the same values
(e.g.,
> > > cnv and CNV represent 2 different things). Anyone know how to make
> > vlookup
> > > case sensitive? Or is there another fancy excel function that can
provide
> > > the equivalent functionality?
> > >
> > > Thanks!
> >
> >
> >

Worked perfectly! Pretty clever - thanks again!

"Peo Sjoblom" wrote:

> =INDEX(D2:D50,MATCH(1,--EXACT(C2:C50,A1),0))
>
> also array entered
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "Dan in NY" <[email protected]> wrote in message
> news:[email protected]...
> > Getting closer, but the FIND pulls in an earlier lookup decode that simply
> > contains the searched for item. For example, if one of my lookup values
> is
> > "F", your solution returns the column B for "CFD" - I assume since it
> finds
> > the "F" in "CFD" before it gets to the "F" entry (the lookup decode list
> is
> > in alphabetical order). hmm...tricky.
> >
> > Any more suggestions?
> >
> > Thanks!
> >
> > "Peo Sjoblom" wrote:
> >
> > > One way (albeit replacing vlookup with another formula)
> > >
> > > Assume the vlookup would look like this
> > >
> > > =VLOOKUP(A1,C2:D50,2,0)
> > >
> > > to lookup a case sensitive string from A1 in column C and return the
> values
> > > from D would be
> > >
> > > =INDEX(D2:D50,MATCH(1,--ISNUMBER(FIND(A1,C2:C50)),0))
> > >
> > > entered with ctrl + shift & enter
> > >
> > >
> > >
> > > --
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > >
> > > "Dan in NY" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I'm using vlookup for translating a code into a decode value. The
> codes
> > > are
> > > > in both lowercase and uppercase, unfortunately with the same values
> (e.g.,
> > > > cnv and CNV represent 2 different things). Anyone know how to make
> > > vlookup
> > > > case sensitive? Or is there another fancy excel function that can
> provide
> > > > the equivalent functionality?
> > > >
> > > > Thanks!
> > >
> > >
> > >
>
>
>