Simple lookup problem...

[Haxer]

I am trying to do a simple lookup command. Or at least I thought it was
simple. I don't know how many items will be in column A but I do know that it
won't go past row 1000. This is what I am using:
=LOOKUP(V3,S3:S1000,A3:A1000)
I keep returning a 0 for the answer. If I change it so that it only looks at
cells that curently have data:
=LOOKUP(V3,S3:S10,A3:A10)
it works fine, problem being that more items will be added, and yes the
cells are sorted.
Can anyone tell me where I am making my mistake please?
Thank you for your time.

 

[aidan.heritage]

I am trying to do a simple lookup command. Or at least I thought it was
simple. I don't know how many items will be in column A but I do know that it
won't go past row 1000. This is what I am using:
=LOOKUP(V3,S3:S1000,A3:A1000)
I keep returning a 0 for the answer. If I change it so that it only looks at
cells that curently have data:
=LOOKUP(V3,S3:S10,A3:A10)
it works fine, problem being that more items will be added, and yes the
cells are sorted.
Can anyone tell me where I am making my mistake please?
Thank you for your time.

 

[John Bundy]

Assuming that the answer is not 0 I don't know your code worked for me, try
looking into an index/match and see if you get the same result
=INDEX(A3:A1000,MATCH(V3,S3:S1000))

 

[aidan.heritage]

I am trying to do a simple lookup command. Or at least I thought it was
simple. I don't know how many items will be in column A but I do know that it
won't go past row 1000. This is what I am using:
=LOOKUP(V3,S3:S1000,A3:A1000)
I keep returning a 0 for the answer. If I change it so that it only looks at
cells that curently have data:
=LOOKUP(V3,S3:S10,A3:A10)
it works fine, problem being that more items will be added, and yes the
cells are sorted.
Can anyone tell me where I am making my mistake please?
Thank you for your time.

At face value, it should be fine - but you could create two
dynamically named ranges - for example


=Sheet1!$S$3:OFFSET(Sheet1!$s$2,COUNTA(Sheet1!$s$3:$s$1200),0)

would give you a range that is as long as the data in S3:S1200, but
ONLY as long as the data

 

[Haxer]

Thank yo for your reply. It did not however work. I did just realize that
there is a formula in the cells (column s) that it is compairing, pretty sure
this could be the problem. Don't understand why it would work if I use 10
instead of 1000 places to search. Is there a formula to make it compair the
value of a cell, not the formula?
Thanks again for your help.

 

[aidan.heritage]

Thank yo for your reply. It did not however work. I did just realize that
there is a formula in the cells (column s) that it is compairing, pretty sure
this could be the problem. Don't understand why it would work if I use 10
instead of 1000 places to search. Is there a formula to make it compair the
value of a cell, not the formula?
Thanks again for your help.






- Show quoted text -

shouldn't make any difference, so possibly the cell is returning
something similar to but not the same as the look up value (eg text
instead of number, extra space)

if you want to email me the file ([email protected]) I may be
able to spot something that explains it, or come up with an
alternative method.