LOOKUP function not returning expected value - Using vector_lookup format

Hi

I am running this lookup function but can't get the correct result, anyone know what I am doing wrong

=LOOKUP(AI61,{"NearExactExactExact","ExactNearExactExact","ExactNearExactDifferent","ExactDifferentExactExact","ExactDifferentExactDifferent","ExactAbsentExactExact","ExactAbsentExactDifferent","ExactExactExactExact","ExactExactExactDifferent","NearExactExactDifferent",90,93,97},{"A4","A1","A2","A2","A3","A2","A3","A1","A2","A3","A-","A","A+"})

value in AI61 is NearExactExactExact, but it is returning A-, I was expecting it to return A4?

Try this instead

=VLOOKUP(AI61,{"NearExactExactExact","A4";"ExactNearExactExact","A1";"ExactNearExactDifferent","A2";"ExactDifferentExactExact","A2";"ExactDifferentExactDifferent","A3";"ExactAbsentExactExact","A2";"ExactAbsentExactDifferent","A3";"ExactExactExactExact","A1";"ExactExactExactDifferent","A2";"NearExactExactDifferent","A3";90,"A-";93,"A";97,"A+"},2,0)



LOOKUP needs to have the lookup vector sorted in ascending order

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"JerichoForce" <JerichoForce.26fxob_1145331308.0442@excelforum-nospam.com>
wrote in message
news:JerichoForce.26fxob_1145331308.0442@excelforum-nospam.com...
>
> Hi
>
> I am running this lookup function but can't get the correct result,
> anyone know what I am doing wrong
>
> =LOOKUP(AI61,{"NearExactExactExact","ExactNearExactExact","ExactNearExactDifferent","ExactDifferentExactExact","ExactDifferentExactDifferent","ExactAbsentExactExact","ExactAbsentExactDifferent","ExactExactExactExact","ExactExactExactDifferent","NearExactExactDifferent",90,93,97},{"A4","A1","A2","A2","A3","A2","A3","A1","A2","A3","A-","A","A+"})
>
> value in AI61 is NearExactExactExact, but it is returning A-, I was
> expecting it to return A4?
>
>
> --
> JerichoForce
> ------------------------------------------------------------------------
> JerichoForce's Profile:
> http://www.excelforum.com/member.php...o&userid=33584
> View this thread: http://www.excelforum.com/showthread...hreadid=533623
>

if you use the fourth argument as false vlookup will look for an exact
match..and you dont need to sort
=VLOOKUP(AI61,{"NearExactExactExact","A4";"ExactNearExactExact","A1";"ExactNearExactDifferent","A2";"ExactDifferentExactExact","A2";"ExactDifferentExactDifferent","A3";"ExactAbsentExactExact","A2";"ExactAbsentExactDifferent","A3";"ExactExactExactExact","A1";"ExactExactExactDifferent","A2";"NearExactExactDifferent","A3";90,"A-";93,"A";97,"A+"},2,false)

--
paul
paul.shepherd@nospamparadise.net.nz
remove nospam for email addy!



"JerichoForce" wrote:

>
> Hi
>
> I am running this lookup function but can't get the correct result,
> anyone know what I am doing wrong
>
> =LOOKUP(AI61,{"NearExactExactExact","ExactNearExactExact","ExactNearExactDifferent","ExactDifferentExactExact","ExactDifferentExactDifferent","ExactAbsentExactExact","ExactAbsentExactDifferent","ExactExactExactExact","ExactExactExactDifferent","NearExactExactDifferent",90,93,97},{"A4","A1","A2","A2","A3","A2","A3","A1","A2","A3","A-","A","A+"})
>
> value in AI61 is NearExactExactExact, but it is returning A-, I was
> expecting it to return A4?
>
>
> --
> JerichoForce
> ------------------------------------------------------------------------
> JerichoForce's Profile: http://www.excelforum.com/member.php...o&userid=33584
> View this thread: http://www.excelforum.com/showthread...hreadid=533623
>
>