Lookup with a bizarre twist
 

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

RFJ wrote:
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

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


"RFJ" wrote:

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

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!

In article ,
"RFJ" wrote:

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

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

In article ,
Domenic wrote:

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!