Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
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.officekb.com |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match 3 Criteria and Return Lowest Numeric Value | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Using Match with multiple criteria | Excel Worksheet Functions | |||
match multiple criteria & return value from array | Excel Worksheet Functions |