MATCH Multiple Criteria & Return Previous / Penultimate Match

  • Thread starter Thread starter Sam via OfficeKB.com
  • Start date Start date
Also, make sure that you select BA7 (actually, I see that you'll now be
entering the formula in AZ7 and copying it down) first, before defining
your named ranges.
 
With the layout as you described in your previous post, try the
following...

1) Select 'Update Appraisal'!AZ7 and define the following:

ID:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

Data:

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536)),,,,51)

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Array1:

=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

Array2:

=ISNUMBER(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+Array1,""),IF(Range="",ROW(Range)),0))+0

Array3:

=MMULT(Array2,ROW(INDIRECT("1:"&'Update Appraisal'!$BB7))^0)

Array4:

=ISNA(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range)),0))

2) Then enter the following formula in 'Update Appraisal'!AZ7 and copy
down:

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

Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Hi Domenic,

I am now getting most of the expected ID results. However, I still have a
problem returning the correct ID result where zero "0" EmptyText rows needs
to be matched.
It looks like you'll have to adjust the references for the other defined
names which refer to Data. So, for example, if the first cell in which
the formula will be entered is BA7, first define the name Range as
follows...

Then replace any reference to Data or 'Site Lond'!Data with Range. See
if this helps.
Click to expand...

This has helped.

Further help appreciated.

Cheers,
Sam
 
Hi Domenic,

I have not tried the solution below as yet. We may be at cross-purposes.

The only problem with the existing solution is, if I wish to locate an
EmptyText row, that is; when the match of EmptyText is 0: there should be two
sequential row matches of the same TEXT.

For example, to find the ID of the penultimate EmptyText row of zero (0) for
TEXT "1" should return ID 1303.

Sample Data Layout:
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306
1307
1308 1
1309
1310 1
1311
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320 1

I am now getting most of the expected ID results. However, I still have a
problem returning the correct ID result where zero "0" EmptyText rows needs
to be matched.

Cheers,
Sam

With the layout as you described in your previous post, try the
following...
Click to expand...
1) Select 'Update Appraisal'!AZ7 and define the following:

=OFFSET('Site Lond'!$A$12,0,0,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536))

=OFFSET('Site Lond'!$H$12:INDEX('Site
Lond'!$H$12:$H$65536,MATCH(9.99999999999999E+307,'Site
Lond'!$A$12:$A$65536)),,,,51)

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

=TRANSPOSE(ROW(INDIRECT("1:"&'Update Appraisal'!$BB7)))

=ISNUMBER(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+Array1,""),IF(Range="",ROW(Range)),0))+0

=MMULT(Array2,ROW(INDIRECT("1:"&'Update Appraisal'!$BB7))^0)

=ISNA(MATCH(IF(Range=LEFT('Update Appraisal'!$AX7,LEN('Update
Appraisal'!$AX7)),ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range)),0))
Click to expand...
 
To clarify, if the data contained the following...

ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306 1
1307
1308 1
1309 1
1310 1
1311 1
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320

....what would you expect as the result?
 
Hi Domenic,

I see what you mean, a bit confusing. I did have to pause for thought. I
would expect ID 1311.

My qualification or rule of thumb for a penultimate zero (0) EmptyText row
was not made clear. I should have said AT LEAST 2 sequential or consecutive
TEXT rows. In your example, ID 1312 would be the LAST and ID 1311 would be
the penultimate.

Cheers,
Sam
To clarify, if the data contained the following...
ID "Col1"
1300 1
1301
1302 1
1303 1
1304
1305 1
1306 1
1307
1308 1
1309 1
1310 1
1311 1
1312 1
1313
1314
1315
1316 1
1317
1318
1319 1
1320
Click to expand...
...what would you expect as the result?
Click to expand...

ID 1311
 
First, select AZ7 and define the following...

Range:

=INDEX(Data,0,ROWS('Update Appraisal'!$AZ$7:$AZ7))

Txt:

=LEFT('Update Appraisal'!$AX7,LEN('Update Appraisal'!$AX7))

Array1:

=IF('Update Appraisal'!$BB7>0,TRANSPOSE(ROW(INDIRECT("1:"&'Update
Appraisal'!$BB7))),1)

Array2:

=ISNUMBER(MATCH(IF(Range=Txt,ROW(Range)+IF('Update
Appraisal'!$BB7>0,Array1,1),""),IF(Range=IF('Update
Appraisal'!$BB7>0,"",Txt),ROW(Range)),0))+0

Array3:

=MMULT(Array2,IF('Update Appraisal'!$BB7>0,ROW(INDIRECT("1:"&'Update
Appraisal'!$BB7))^0,1))

Array4:

=ISNA(MATCH(IF(Range=Txt,ROW(Range)+'Update
Appraisal'!$BB7+1,""),IF(Range="",ROW(Range),""),0))

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

AZ7, copied down:

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

Hope this helps!
 
Hi Domenic,

That's done it. Absolutely Superb! Thank you very much for all your time and
patience.

Very much appreciated.

Cheers,
Sam
 
Back
Top