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 Martin wrote:
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


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
---------------
Jacob Skaria


"Connie Martin" wrote:

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

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

"Jacob Skaria" wrote:

Try VLOOKUP instead with an exact match as below

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

If this post helps click Yes
---------------
Jacob Skaria


"Connie Martin" wrote:

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

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

"Glenn" wrote:

Connie Martin wrote:
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


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?
.

No. The help file is your friend...


VLOOKUP(lookup_value,table_array,col_index_num,ran ge_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.


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

"Glenn" wrote:

Connie Martin wrote:
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

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?
.