Return value 1 row above non-blank cell in range
 

Hi everyone,

I've been searching through functions but to no avail.

I would like to find a way to search a range to find a cell with a value in
it, then I would like the value above to be returned.

e.g if range is B9:F9 and the cell with a value in is B9 then I would like
the value in B8 returned.

I thought I could do it in stages using MAX to find value then Index and
Match to find cell ref (Duh!) and then OFFSET to return value above.

Please help!

Cheers
--
Mifty

Return value 1 row above non-blank cell in range
 

One way:

It's hard to tell what you're searching for, but if you're looking for
'x', then

=INDEX(B8:F8,MATCH(x, B9:F9, FALSE))

In article ,
Mifty wrote:

Hi everyone,

I've been searching through functions but to no avail.

I would like to find a way to search a range to find a cell with a value in
it, then I would like the value above to be returned.

e.g if range is B9:F9 and the cell with a value in is B9 then I would like
the value in B8 returned.

I thought I could do it in stages using MAX to find value then Index and
Match to find cell ref (Duh!) and then OFFSET to return value above.

Please help!

Cheers

Return value 1 row above non-blank cell in range
 

Assuming that one cell in B9:F9 is non-blank, then:

=OFFSET(B8,0,MATCH(LOOKUP(99^99,B9:F9),B9:F9)-1)

will return the value in the cell above it.
--
Gary''s Student - gsnu200785


"Mifty" wrote:

Hi everyone,

I've been searching through functions but to no avail.

I would like to find a way to search a range to find a cell with a value in
it, then I would like the value above to be returned.

e.g if range is B9:F9 and the cell with a value in is B9 then I would like
the value in B8 returned.

I thought I could do it in stages using MAX to find value then Index and
Match to find cell ref (Duh!) and then OFFSET to return value above.

Please help!

Cheers
--
Mifty

Return value 1 row above non-blank cell in range
 

Hi JE,

I'm looking for values 1-5.
I've just tried Gary's Student's solution and it does the trick but I've
copied and pasted your formula for future learning.

Many thanks
--
Mifty


"JE McGimpsey" wrote:

One way:

It's hard to tell what you're searching for, but if you're looking for
'x', then

=INDEX(B8:F8,MATCH(x, B9:F9, FALSE))

In article ,
Mifty wrote:

Hi everyone,

I've been searching through functions but to no avail.

I would like to find a way to search a range to find a cell with a value in
it, then I would like the value above to be returned.

e.g if range is B9:F9 and the cell with a value in is B9 then I would like
the value in B8 returned.

I thought I could do it in stages using MAX to find value then Index and
Match to find cell ref (Duh!) and then OFFSET to return value above.

Please help!

Cheers

Return value 1 row above non-blank cell in range
 

Wow, thank you Gary's Student

Any chance you could explain how it works please?

--
Mifty


"Gary''s Student" wrote:

Assuming that one cell in B9:F9 is non-blank, then:

=OFFSET(B8,0,MATCH(LOOKUP(99^99,B9:F9),B9:F9)-1)

will return the value in the cell above it.
--
Gary''s Student - gsnu200785


"Mifty" wrote:

Hi everyone,

I've been searching through functions but to no avail.

I would like to find a way to search a range to find a cell with a value in
it, then I would like the value above to be returned.

e.g if range is B9:F9 and the cell with a value in is B9 then I would like
the value in B8 returned.

I thought I could do it in stages using MAX to find value then Index and
Match to find cell ref (Duh!) and then OFFSET to return value above.

Please help!

Cheers
--
Mifty