VLOOKUP
 

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

VLOOKUP
 

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 :U1100)),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:U1100)),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.


stew wrote:

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

--

Dave Peterson

VLOOKUP
 

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

This is an array formula that must be entered with CNTRL-SHFT-ENTER and not
just the ENTER key.
--
Gary''s Student - gsnu200810


"stew" wrote:

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

VLOOKUP
 

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:U1100,"Yes",""), 0))
and press CTRL-SHIFT-ENTER together.

"stew" wrote:
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

VLOOKUP
 

Again thanks to all for your help

stew
"Dave Peterson" wrote:

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 :U1100)),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:U1100)),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.


stew wrote:

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

--

Dave Peterson