Return value 1 row above non-blank cell in range

[Mifty]

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

 

[JE McGimpsey]

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))

 

[Gary''s Student]

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.

 

[Mifty]

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]

Wow, thank you Gary's Student

Any chance you could explain how it works please?