Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Problem | Excel Worksheet Functions | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Excel Lookup Functions | Excel Worksheet Functions | |||
Another Lookup function, please | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |