VLOOKUP

S

stew

Hi all

I have this in u112

=VLOOKUP(>0,U10:U110,1)

With the intention of finding the the first number above zero, STARTING FROM
U110 AND SEARCHING THROUGH U10 and displaying THE RESULT, IF ANY, in U112

DOES NOT WORK

HELP
 
D

Dave Peterson

If you were starting at U10 and looking down through U110, you could use this
formula:
=INDEX(U10:U110,MATCH(1,((ISNUMBER(U10:U110))*(U10:U110>0)),0))

But since you want to start at U110 and look up through U10, you could use this
formula:
=LOOKUP(2,1/(ISNUMBER(U10:U110)*(U10:U110>0)),U10:U110)

Both of these are array formulas.

Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap
curly brackets {} around your formula. (don't type them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.
 
G

Gary''s Student

=INDEX(U10:U100,MATCH(TRUE,U10:U100>0,0))

This is an array formula that must be entered with CNTRL-SHFT-ENTER and not
just the ENTER key.
 
S

Sheeloo

First argument of VLOOKUP has to evaluate to a value, it can not be a conditon.

Type or paste this in U112
=INDIRECT("U"&MATCH("Yes",IF(U10:U110>0,"Yes",""),0))
and press CTRL-SHIFT-ENTER together.
 
S

stew

Again thanks to all for your help

stew
Dave Peterson said:
If you were starting at U10 and looking down through U110, you could use this
formula:
=INDEX(U10:U110,MATCH(1,((ISNUMBER(U10:U110))*(U10:U110>0)),0))

But since you want to start at U110 and look up through U10, you could use this
formula:
=LOOKUP(2,1/(ISNUMBER(U10:U110)*(U10:U110>0)),U10:U110)

Both of these are array formulas.

Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap
curly brackets {} around your formula. (don't type them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.
Click to expand...
 
Top