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 http://www.yqcomputer.com/

i All,

Just to clarify.

The Criteria to Match:

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

In Column1 - Match one EmptyText row after / below single occurrence of Text

value zero "0". (changing criteria).

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.

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:

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200709/1

Just to clarify.

The Criteria to Match:

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

In Column1 - Match one EmptyText row after / below single occurrence of Text

value zero "0". (changing criteria).

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.

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:

--

Message posted via OfficeKB.com

http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200709/1

Try...

=IF(AW7<>"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T

EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Data)-1)="")))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <78e69a99cec71@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

=IF(AW7<>"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T

EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Data)-1)="")))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <78e69a99cec71@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Hi Domenic,

Thank you very much for reply and assistance.

The first cell returned the LAST ID occurrence of the matched text pattern. I

require the previous / penultimate occurrence of the matched pattern. The

following cells returned error value #N/A.

The Formula:

1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS

($1:1) , so that I can access the correct columns when the Formula is filled

down.

2) I cannot see where in the Formula the varied number of EmptyText rows is

matched. This varies for each cell row and also needs to be filled down. In

my non-working version of the Formula, I was using cell AZ7 to hold the

number of emptyText rows I need to match after the Text value.

3) The Text value and the number of EmptyText rows to match are both variable

criteria.

4) Amending the Formula as in 1 above, all cells return the LAST matched

occurrence, but I require the previous / penultimate occurrence of the

matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7).

These criteria will fill down with each row.

Further assistance appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Thank you very much for reply and assistance.

The first cell returned the LAST ID occurrence of the matched text pattern. I

require the previous / penultimate occurrence of the matched pattern. The

following cells returned error value #N/A.

The Formula:

1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS

($1:1) , so that I can access the correct columns when the Formula is filled

down.

2) I cannot see where in the Formula the varied number of EmptyText rows is

matched. This varies for each cell row and also needs to be filled down. In

my non-working version of the Formula, I was using cell AZ7 to hold the

number of emptyText rows I need to match after the Text value.

3) The Text value and the number of EmptyText rows to match are both variable

criteria.

4) Amending the Formula as in 1 above, all cells return the LAST matched

occurrence, but I require the previous / penultimate occurrence of the

matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7).

These criteria will fill down with each row.

Further assistance appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Assuming that the formula will be entered in BA7 and copied down, try

the following...

1) Select cell BA7

2) Define the following...

Insert > Name > Define

Name: Range

Refers to:

=INDEX(Data,0,ROWS($BA$7:$BA7))

Click Add

Name: Arrray1

Refers to:

=TRANSPOSE(ROW(INDIRECT("1:"&$AZ7)))

Click Add

Name: Array2

Refers to:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range=TEXT($AX7,0),ROW(Range)

+Array1,"")),IF(Range="",ROW(Range)),0))+0)

Click Ok

3) Enter the following formula in BA7, and copy down:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2))

...confirmed with CONTROL+SHIFT+ENTER

**Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be

replaced with $AX7.

Hope this helps!

In article <78e8c043e0a20@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

the following...

1) Select cell BA7

2) Define the following...

Insert > Name > Define

Name: Range

Refers to:

=INDEX(Data,0,ROWS($BA$7:$BA7))

Click Add

Name: Arrray1

Refers to:

=TRANSPOSE(ROW(INDIRECT("1:"&$AZ7)))

Click Add

Name: Array2

Refers to:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range=TEXT($AX7,0),ROW(Range)

+Array1,"")),IF(Range="",ROW(Range)),0))+0)

Click Ok

3) Enter the following formula in BA7, and copy down:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2))

...confirmed with CONTROL+SHIFT+ENTER

**Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be

replaced with $AX7.

Hope this helps!

In article <78e8c043e0a20@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Hi Domenic,

Thank you for your time and assistance.

1) The Formula returns incorrect results with some #REF! errors. I think the

#REF! errors may be from =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))). Not sure?

2) Also, I think the Formula is trying to return the penultimate ID that

matches the specific TEXT criteria above the EmptyText rows. I require the

penultimate ID that matches first, Text criteria and then the specific number

of variable EmptyText rows below the Text criteria. The penultimate ID

returned should match with the last EmptyText row criteria.

Further assistance most appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Thank you for your time and assistance.

1) The Formula returns incorrect results with some #REF! errors. I think the

#REF! errors may be from =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))). Not sure?

2) Also, I think the Formula is trying to return the penultimate ID that

matches the specific TEXT criteria above the EmptyText rows. I require the

penultimate ID that matches first, Text criteria and then the specific number

of variable EmptyText rows below the Text criteria. The penultimate ID

returned should match with the last EmptyText row criteria.

Further assistance most appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Let's assume the following...

A2:A22 contains the ID, and named ID

B2:B22 contains the data, and named Data

AX7 contains the text value to match

AZ7 contains the the number of empty text values to match

A2:B22 contains the following...

1300 1

1301 Empty Text

1302 1

1303 2

1304 Empty Text

1305 1

1306 Empty Text

1307 Empty Text

1308 1

1309 Empty Text

1310 Empty Text

1311 Empty Text

1312 1

1313 Empty Text

1314 Empty Text

1315 1

1316 2

1317 Empty Text

1318 Empty Text

1319 Empty Text

1320 1

AX7 contains 1

AZ7 contains 2

So if we're trying to find the penultimate ID where the text value 1 is

followed by 2 empty text values, the formula should return 1310. If

this is correct, try the following...

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Data=TEXT(Sheet1!$AX7,0),ROW(

Data)+Array1,"")),IF(Data="",ROW(Data)),0))+0)

BA7:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2)+$AZ7

)

...confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

In article <7901866312afb@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

A2:A22 contains the ID, and named ID

B2:B22 contains the data, and named Data

AX7 contains the text value to match

AZ7 contains the the number of empty text values to match

A2:B22 contains the following...

1300 1

1301 Empty Text

1302 1

1303 2

1304 Empty Text

1305 1

1306 Empty Text

1307 Empty Text

1308 1

1309 Empty Text

1310 Empty Text

1311 Empty Text

1312 1

1313 Empty Text

1314 Empty Text

1315 1

1316 2

1317 Empty Text

1318 Empty Text

1319 Empty Text

1320 1

AX7 contains 1

AZ7 contains 2

So if we're trying to find the penultimate ID where the text value 1 is

followed by 2 empty text values, the formula should return 1310. If

this is correct, try the following...

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Data=TEXT(Sheet1!$AX7,0),ROW(

Data)+Array1,"")),IF(Data="",ROW(Data)),0))+0)

BA7:

=INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2)+$AZ7

)

...confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

In article <7901866312afb@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Hi Domenic,

Thanks for further assistance.

Using your example below, my expected and required result would be ID 1307.

The number of EmptyText cell rows must be an explicit match. Thereby, a match,

if the number of EmptyText rows is an exact sequential match. If looking for

2 EmptyText cell rows then, there should be exactly 2 EmptyText rows below

the Text for it to be a match.

Cheers,

Sam

No, using your example above, my expected and required result would be ID

1307.

The number of EmptyText cell rows must be an explicit match. Thereby, a match,

if the number of EmptyText rows is an exact sequential match.

--

Message posted via http://www.yqcomputer.com/

Thanks for further assistance.

Using your example below, my expected and required result would be ID 1307.

The number of EmptyText cell rows must be an explicit match. Thereby, a match,

if the number of EmptyText rows is an exact sequential match. If looking for

2 EmptyText cell rows then, there should be exactly 2 EmptyText rows below

the Text for it to be a match.

Cheers,

Sam

No, using your example above, my expected and required result would be ID

1307.

The number of EmptyText cell rows must be an explicit match. Thereby, a match,

if the number of EmptyText rows is an exact sequential match.

--

Message posted via http://www.yqcomputer.com/

In that case, try the following instead...

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=ISNUMBER(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data)+Array1,""),IF(Data=

"",ROW(Data)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0)

Array4:

=ISNA(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data)+Sheet1!$AZ7+1,""),IF(Da

ta="",ROW(Data)),0))

BA7:

=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID)-MIN(ROW(ID))+1)),2)+$AZ7

)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <7903cd033b3d1@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&Sheet1!$AZ7)))

Array2:

=ISNUMBER(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data)+Array1,""),IF(Data=

"",ROW(Data)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&Sheet1!$AZ7))^0)

Array4:

=ISNA(MATCH(IF(Data=TEXT(Sheet1!$AX7,0),ROW(Data)+Sheet1!$AZ7+1,""),IF(Da

ta="",ROW(Data)),0))

BA7:

=INDEX(ID,LARGE(IF(Array3=$AZ7,IF(Array4,ROW(ID)-MIN(ROW(ID))+1)),2)+$AZ7

)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <7903cd033b3d1@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Hi Domenic,

Thank you for all your help.

Using the solution below:

1st cell returns #VALUE! error,

2nd cell #REF! error,

3rd cell #REF! error,

4th cell #NA error.

I've tried parts of the solution in separate cells:

Array 3 returns #VALUE! error.

Formula returns #VALUE! error.

Further help appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Thank you for all your help.

Using the solution below:

1st cell returns #VALUE! error,

2nd cell #REF! error,

3rd cell #REF! error,

4th cell #NA error.

I've tried parts of the solution in separate cells:

Array 3 returns #VALUE! error.

Formula returns #VALUE! error.

Further help appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Sample file sent...

In article <790dc05454656@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

In article <790dc05454656@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Hi Domenic,

Thanks for sample file.

If I try to match zero (0) EmptyText rows Formula returns #REF! error.

Also range "Data" is dynamic with many rows and columns. ID is a single

column dynamic range.

Further help appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

Thanks for sample file.

If I try to match zero (0) EmptyText rows Formula returns #REF! error.

Also range "Data" is dynamic with many rows and columns. ID is a single

column dynamic range.

Further help appreciated.

Cheers,

Sam

--

Message posted via OfficeKB.com

http://www.yqcomputer.com/

The error probably has to do with the reference for the appropriate

range within your named range 'Data', which spans a number of rows and

columns. So you'll need to replace the named range 'Data' in my

formula, which just refers to a single column, with the appropriate

reference for your worksheet.

You had mention that you will be copying the formula down the column.

If you want to pick up an new column each time the formula is copied to

the next cell below, I think you'll need to replace 'Data' in my formula

with INDEX(Data,0,ROWS(BA$7:BA7)). If, however, you want to pick up a

new column each time the formula is copied across instead of down,

replace 'Data' with INDEX(Data,0,COLUMNS($BA7:BA7)).

Does this help?

In article <790f6dd708df7@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

range within your named range 'Data', which spans a number of rows and

columns. So you'll need to replace the named range 'Data' in my

formula, which just refers to a single column, with the appropriate

reference for your worksheet.

You had mention that you will be copying the formula down the column.

If you want to pick up an new column each time the formula is copied to

the next cell below, I think you'll need to replace 'Data' in my formula

with INDEX(Data,0,ROWS(BA$7:BA7)). If, however, you want to pick up a

new column each time the formula is copied across instead of down,

replace 'Data' with INDEX(Data,0,COLUMNS($BA7:BA7)).

Does this help?

In article <790f6dd708df7@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

Hi Domenic,

I made what I thought to be the necessary changes (amended Array2 and Array4)

but getting #Value! error in 1st cell and #REF! error in 2nd cell.

Could you possibly provide a note of the necessary amendments.

Also, using sample file with single column of data to match zero (0)

EmptyText rows, Formula returns #REF! error.

Further assistance appreciated.

Cheers,

Sam

Yes, picking up new column each time the formula is copied to

the next cell below.

--

Message posted via http://www.yqcomputer.com/

I made what I thought to be the necessary changes (amended Array2 and Array4)

but getting #Value! error in 1st cell and #REF! error in 2nd cell.

Could you possibly provide a note of the necessary amendments.

Also, using sample file with single column of data to match zero (0)

EmptyText rows, Formula returns #REF! error.

Further assistance appreciated.

Cheers,

Sam

Yes, picking up new column each time the formula is copied to

the next cell below.

--

Message posted via http://www.yqcomputer.com/

At this point, I think it would be easier if you sent me a sample file

and I'll make the necessary adjustments. Otherwise, for clarity, you'll

have to recap where all the relevant data lies, provide the defined

names and their references, provide the actual formula you're using, and

specify in which cell the formula will first be entered.

In article <791bbc6834d38@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

and I'll make the necessary adjustments. Otherwise, for clarity, you'll

have to recap where all the relevant data lies, provide the defined

names and their references, provide the actual formula you're using, and

specify in which cell the formula will first be entered.

In article <791bbc6834d38@uwe>, "Sam via OfficeKB.com" <u4102@uwe>

1. match/scan does not return multiple matches

2. Match a pattern multiple times, returning matches, captures and offset?

3. Multiple Criteria and return only one match

4. How to match date criteria then calculate an average of matches

5. Four criteria MATCH INDEX lookup of date between matching two

6. RegEx - matching previous match

7. Four criteria MATCH INDEX lookup of date between matching two text

8. regexp: match only if previous matched?

9. If two criteria match then sum matching values in another column

10. Open form that matches criteria from previous form

11. Show only data that matches criteria and previous

12. Matching numbers in an Array and returning values for matched

13. index match array function-returning only first match, need last.

15. return N/A when no first match but do nothing if no second match