Troubleshoot LOOKUP, please

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

Connie Martin

Why does this lookup work sometimes, and sometimes not? Formula is:
=IF(D10="","",LOOKUP(D10,P: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
 
Connie said:
Why does this lookup work sometimes, and sometimes not? Formula is:
=IF(D10="","",LOOKUP(D10,P: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
Click to expand...


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?
 
Try VLOOKUP instead with an exact match as below

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

If this post helps click Yes
 
Glenn, no, column P is not sorted. Does it have to be sorted for VLOOKUP,
as well?
 
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.
 
Back
Top