Hi All,

Just to clarify.

The Criteria to Match:
The MATCH sequence will be:
1) Match specific Text

In Column1 - Single occurrence of Text value zero "0". (changing criteria)

2) Match varying sequential numbers of EMPTY TEXT rows (could be anything
from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order)
NB: When the match of EMPTY TEXT is 0: there should be two sequential row
matches of the same TEXT (as found in number 1 above).

In Column1 - Match one EmptyText row after / below single occurrence of Text
value zero "0". (changing criteria).

3) Return previous / penultimate MATCH of the above (1 & 2).

Return previous / penultimate match of single instance of Text value zero
"0" with one EmptyText row below it.

Expected Result ID 1304.

In the non-working formula below:
Cell AW7 holds the Text value of interest.
Cell AZ7 holds the number of EmptyText rows to match.
The Formula needs to filldown a single column.

=IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1:
1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS
(AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1

Sample Data Layout:
ID, Col1, Col2, Col3,
1300, EmptyText,1, EmptyText,
1301, 00, EmptyText,EmptyText,
1302, 0, 1, EmptyText,
1303, 0, 1, EmptyText,
1304, EmptyText,1, 2,
1305, 00, EmptyText,2,
1306, 0, EmptyText,EmptyText,
1307, 0, EmptyText,EmptyText,
1308, 0, 1, EmptyText,
1309, EmptyText,1, 22,

NB: Commas separate the columns.

1) Match specific Text for Column2
- Single occurrence of Text value "1"

2) Match varying sequential numbers of EMPTY TEXT rows
- Match zero EmptyText row after / below single occurrence of Text value one
"1".
this means two sequential instances of text value "1".

3) Return previous / penultimate MATCH of the above (1 & 2).
- Return previous / penultimate match of single instance of Text value one
"1" with zero EmptyText row below it.

Expected Result ID 1304.

Cheers
Sam

Sam wrote:
Hi All,

I am using a dynamic range called "Data", spanning many rows and columns. The
range holds TEXT data and starts at row number 12, column "H". The oldest
data is in row 12, the start / top of my range; the most recent data is at
the end / bottom of my range.

The value to be returned is numeric and held in a single column, dynamic
range called "ID", adjacent to dynamic range "Data". The return value will be
returned down a single column.
I would like the Formula to be flexible using Input cells to hold the varying
criteria.

The MATCH sequence will be:
1) Match specific Text

2) Match varying sequential numbers of EMPTY TEXT rows (could be anything
from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order)
. NB: When the match of EMPTY TEXT is 0: there should be two sequential row
matches of the same TEXT (as found in number 1 above).

3) Return previous / penultimate MATCH of the above (1 & 2).

I've tried a few variations but still NO eureka!

=IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1:
1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS
(AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1

=IF(AW7="","",INDEX(MATCH(1,(OFFSET('Site Lond'!Appraisal,0,ROWS($1:1)-1,,1)
=TEXT(AW7,0))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS(AZ7)&""))
+OFFSET('Site Lond'!ID,,,,1),0,1))-1

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200709/1