Troubleshoot LOOKUP, please

[Connie Martin]

Why does this lookup work sometimes, and sometimes not? Formula is:
=IF(D10="","",LOOKUP(D10,P,Q:Q)). Right now D10 is giving me the results
in the row above in column Q, if you understand what I mean. The data in D10
is found in P35 and instead of giving me Q35 data, it's giving me Q34. But,
it doesn't always do this. Sometimes it works beautifully. What gives?
Connie

 

[Glenn]

Why does this lookup work sometimes, and sometimes not? Formula is:
=IF(D10="","",LOOKUP(D10,P,Q:Q)). Right now D10 is giving me the results
in the row above in column Q, if you understand what I mean. The data in D10
is found in P35 and instead of giving me Q35 data, it's giving me Q34. But,
it doesn't always do this. Sometimes it works beautifully. What gives?
Connie

LOOKUP(lookup_value,lookup_vector,result_vector)

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.

Is column P sorted?

 

[Jacob Skaria]

Try VLOOKUP instead with an exact match as below

=IF(D10="","",VLOOKUP(D10,P:Q,2,0))

If this post helps click Yes

 

[Connie Martin]

Thank you, Jacob. That works better. Appreciate your help. Connie

 

[Connie Martin]

Glenn, no, column P is not sorted. Does it have to be sorted for VLOOKUP,
as well?

 

[Glenn]

No. The help file is your friend...


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If range_lookup is TRUE, the values in the first column of table_array must be
placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise VLOOKUP may not give the correct value.

If range_lookup is FALSE, table_array does not need to be sorted.



The solution given by Jacob has range_lookup as 0, which Excel interprets the
same as false, which is why it worked for you.