Lookup with a bizarre twist

[RFJ]

In each cell of column B (rows 3 to 150), there is either nothing (Null) or
an asterisk. All the asterisked cells will be in consecutive rows - but
where they start and finish is variable depending on other calculations in
the worksheet.

I want to be able to find the top and bottom row that has an asterisk in it
and return the corresponding cell values in column A.

eg

a
b
c *
d *
e *
f *
g
h

I'd be looking for two formulae - one to identify 'c' as the top value and
'f' as the bottom value.

Can SKS help.

Many thanks in advance.

Rob

 

[Frank Kabel]

Hi
using array formulas (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A150,MIN(IF(B3:B150="*",ROW(B3:B150))))
and
=INDEX(A1:A150,MAX(IF(B3:B150="*",ROW(B3:B150))))

 

[Guest]

One way, for top

=INDEX(A1:A1000,MATCH("~*",B1:B1000,0))

for bottom

=INDEX(A1:A1000,MAX((B1:B1000="*")*(ROW(B1:B1000))))

the latter entered with ctrl + shift & enter



Regards,

Peo Sjoblom

 

[Domenic]

Also...

Top...

=INDEX(A1:A150,MATCH("~*",B1:B150,0))

Bottom...

=INDEX(A1:A150,MATCH("~*",B1:B150))

OR

=LOOKUP(2,1/(B1:B150="*"),A1:A150)

Hope this helps!

 

[Domenic]

Please disregard the second formula. It doesn't work with other data
interspersed.