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

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



"Daniel" wrote in message
...
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,
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" wrote:

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



"Daniel" wrote in message
...
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

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!
================================================== ===


"Daniel" wrote in message
...
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" wrote:

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



"Daniel" wrote in message
...
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

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


"Daniel" wrote in message
...
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" wrote:

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



"Daniel" wrote in message
...
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

thx


"Peo Sjoblom" wrote:

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


"Daniel" wrote in message
...
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" wrote:

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



"Daniel" wrote in message
...
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

Daniel wrote...
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.