LOOKUP function

[wgd.roaming]

OS =XP Pro
Office 3

LOOKUP function, VERY simple formula, just will not work. Is there
something special, an unobvious characteristic about this one?

Playing back how I am using it :
=LOOKUP(x1,x3:x20,y3:y20) where search in column x3 through x20, for
value in x1, returns corresponding contents in adjacent column y3
through y20. Simple. Right? Why will it not work?

Appreciative,
Wayne

 

[joeu2004]

LOOKUP function, VERY simple formula, just will not work.
Is there something special, an unobvious characteristic
about this one?

No way to know since you neglect to tell us what you mean by "will not
work". Does it return a #N/A error or simply unexpected results? Or
something else altogether?

Playing back how I am using it :
=LOOKUP(x1,x3:x20,y3:y20)  where search in column x3
through x20, for value in x1, returns corresponding
contents in adjacent column y3 through y20.

You can assume we know how LOOKUP works. What we need to know is:
what are the contents of X1 and X3:X20?

In the absence of reasonable information, my guess: X3:X20 is not in
ascending order.

But it could just as easily be that the type of X1 and some/all
contents of X3:X20 do not agree. The devil is in the details.

 

[Ian Riches]

In the absence of reasonable information, my guess: X3:X20 is not in
ascending order.

Seconded. From the Help file on lookup function:

"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 it's not this, then we'll need a lot more detail to diagnose the
issue.

Ian

 

[wgd.roaming]

Seconded. From the Help file on lookup function:

"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 it's not this, then we'll need a lot more detail to diagnose the
issue.

Ian

Ian - Thank You. Column that is being search is either a 0 or a 1.
That is it. The column that where the returned data is coming from
is text, names in this case.

For my comment: "it doesn't work", is vague I suppose. The equation
is right; however, nothing happens.

On another sheet, same LOOK function, just longer columns, the
function works fine.

Thus why on one sheet, it doesn't work, on another is works fine.

Thanks for your replies.