lookup error???

[Guest]

why lookup is not working and returning #N/A ? thanks

doing :
=LOOKUP(PRET,G4:G13,F4:F13) on the range below,
the result should be 990, it works only for the first 8 items in the lookup
array

PRET

990 SIM1
990 SIM2
990 SIM3
990 SIM4
990 SIM5
990 SIM6
990 TLOW
990 THIGH
990 PRET
990 POST

 

[Peo Sjoblom]

Here's a good tip, before posting a question take a peek into help,
Here's a quote

"Important The values in lookup_vector must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent."



If you look at yours you can see that the values are not in ascending order




--


Regards,


Peo Sjoblom

 

[Guest]

Peo,
I did looked at that, however LOOKUP does well for the first 8 rows, then
for the 9th and 10th it crashes?
is there an equivalent to LOOKUP that works for vectors in any order?
Dan

 

[RagDyeR]

I'm interested in how you were able to make your formula work at all, since
you don't have PRET enclosed in quotes.
Unless it was just a typo.

Anyway, try this:

=INDEX(F4:F13,MATCH("PRET",G4:G13,0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Peo,
I did looked at that, however LOOKUP does well for the first 8 rows, then
for the 9th and 10th it crashes?
is there an equivalent to LOOKUP that works for vectors in any order?
Dan

 

[Peo Sjoblom]

It says "May not give", so it is not to be trusted I guess, it worked on the
8th and gave a wrong answer on the 9th
Anyway, here's a solution that doesn't need to be in ascending order

=INDEX(G4:G13,MATCH("PRET",F4:F13,0))

the last zero tell MATCH to look for an exact match


--


Regards,


Peo Sjoblom

 

[Guest]

thx

It says "May not give", so it is not to be trusted I guess, it worked on the
8th and gave a wrong answer on the 9th
Anyway, here's a solution that doesn't need to be in ascending order

=INDEX(G4:G13,MATCH("PRET",F4:F13,0))

the last zero tell MATCH to look for an exact match


--


Regards,


Peo Sjoblom

 

[Harlan Grove]

I did looked at that, however LOOKUP does well for the first 8
rows, then for the 9th and 10th it crashes?

....

A stopped clock (of the old mechanical analog variety) is right twice
a day. This is the formula counterpart. Your formula works sometimes
due to pure happenstance. And if the values in your col F range
varied, you'd see that your formula actually only works reliably for
the first 6 rows. LOOKUP("TLOW",G4:G13,F4:F13) actually returns the
value of F13 rather than F10.