| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Multiple Criteria & Return Numeric Labels across single row
Hi All,
I would like a flexible formula that can match multiple criteria and return the results across a single row in consecutive cells. Data Location: Criteria: Month (custom format "mmm") housed in cell B1 Criteria: Values between Lower & Upper Limit (dynamic, will vary). Lower Limit housed in B2, Upper Limit housed in B3. Criteria Limits example: Lower =120, Upper <=150 Return Results: Numeric Labels housed in cells / row C4:BM4 Data: Months (custom format "mmm") Jan to Dec housed in cells / column B5:B16 Data: Numeric Values housed in cells C5:BM16 Scenario: The month criteria housed in cell B1 tells me which ROW of data (within C5: BM16) should have the criteria Lower & Upper Limits applied. The values of the relevant ROW (within C5:BM16) that fulfill the criteria should then have their corresponding Numeric Labels (housed in C4:BM4) returned across a sinlge row in consecutive cells. Sample Data Layout: (8 columns, 12 rows: Jan to Dec) May Criteria Month 120 Lower Limit 150 Upper Limit Labels 1 2 3 4 5 6 7 8 Jan 123 180 165 165 180 119 145 180 Feb 165 119 150 150 119 165 123 100 Mar 119 145 165 150 170 119 170 170 Apr 119 165 119 123 150 145 180 170 May 180 150 165 165 145 150 150 180 Jun 150 170 112 145 145 123 11 145 Jul 150 170 119 170 123 165 150 123 Aug 165 123 170 119 180 119 123 11 Sep 145 165 170 145 145 150 170 150 Oct 150 170 165 150 145 180 180 123 Nov 123 119 145 165 150 119 112 180 Dec 123 150 112 11 145 165 180 119 Expected Results: Numeric Labels (housed in C4:BM4) 2, 5, 6, 7. Based on the criteria Month in cell B1, the Lower & Upper Limits should be applied to row 5 of my data, which corresponds to the month of May. Looking for values in May (row 5) that meet criteria of =120 (greater than or equal to 120) and <=150 (less than or equal to 150); then return their corresponding labels: 2, 5, 6, 7. Thanks Sam P.S., Tried to use the MATCH(1,(criteria)*(criteria)) etc. in some of my attempts! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Based on your posted sample data...
I used several defined names...(use your actual sheet name) Array: refers to: =COLUMN(Table)-MIN(COLUMN(Table))+1 Count: refers to: =Sheet1!$B$4 Headers: refers to: =Sheet1!$C$4:$J$4 Lower: refers to: =Sheet1!$B$2 Month: refers to: =Sheet1!$B$1 Table: refers to: =Sheet1!$C$5:$J$16 Upper: refers to: =Sheet1!$B$3 Enter this formula in B4: =SUMPRODUCT(--(INDEX(Table,MONTH(Month),)=Lower),--(INDEX(Table,MONTH(Month),)<=Upper)) That will return the number of values that meet the criteria and act as an error trap check cell. Enter this array formula** in C18 and copy across until you get blanks: =IF(COLUMNS($C18:C18)<=Count,INDEX(Headers,SMALL(I F((INDEX(Table,MONTH(Month),)=Lower)*(INDEX(Table ,MONTH(Month),)<=Upper),Array),COLUMNS($C18:C18))) ,"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:84fcea97b219e@uwe... Hi All, I would like a flexible formula that can match multiple criteria and return the results across a single row in consecutive cells. Data Location: Criteria: Month (custom format "mmm") housed in cell B1 Criteria: Values between Lower & Upper Limit (dynamic, will vary). Lower Limit housed in B2, Upper Limit housed in B3. Criteria Limits example: Lower =120, Upper <=150 Return Results: Numeric Labels housed in cells / row C4:BM4 Data: Months (custom format "mmm") Jan to Dec housed in cells / column B5:B16 Data: Numeric Values housed in cells C5:BM16 Scenario: The month criteria housed in cell B1 tells me which ROW of data (within C5: BM16) should have the criteria Lower & Upper Limits applied. The values of the relevant ROW (within C5:BM16) that fulfill the criteria should then have their corresponding Numeric Labels (housed in C4:BM4) returned across a sinlge row in consecutive cells. Sample Data Layout: (8 columns, 12 rows: Jan to Dec) May Criteria Month 120 Lower Limit 150 Upper Limit Labels 1 2 3 4 5 6 7 8 Jan 123 180 165 165 180 119 145 180 Feb 165 119 150 150 119 165 123 100 Mar 119 145 165 150 170 119 170 170 Apr 119 165 119 123 150 145 180 170 May 180 150 165 165 145 150 150 180 Jun 150 170 112 145 145 123 11 145 Jul 150 170 119 170 123 165 150 123 Aug 165 123 170 119 180 119 123 11 Sep 145 165 170 145 145 150 170 150 Oct 150 170 165 150 145 180 180 123 Nov 123 119 145 165 150 119 112 180 Dec 123 150 112 11 145 165 180 119 Expected Results: Numeric Labels (housed in C4:BM4) 2, 5, 6, 7. Based on the criteria Month in cell B1, the Lower & Upper Limits should be applied to row 5 of my data, which corresponds to the month of May. Looking for values in May (row 5) that meet criteria of =120 (greater than or equal to 120) and <=150 (less than or equal to 150); then return their corresponding labels: 2, 5, 6, 7. Thanks Sam P.S., Tried to use the MATCH(1,(criteria)*(criteria)) etc. in some of my attempts! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Hi Biff,
That's Brilliant! Thank you very much for all your time and assistance. Most appreciated. Month: refers to: =Sheet1!$B$1 Just in case anyone else views this Thread, my Month in cell B1 was the custom format "mmm", so I referenced another cell that used the full date format to get the month serial number to accomplish MONTH(Month). Cheers, Sam T. Valko wrote: Based on your posted sample data... I used several defined names...(use your actual sheet name) Array: refers to: =COLUMN(Table)-MIN(COLUMN(Table))+1 Count: refers to: =Sheet1!$B$4 Headers: refers to: =Sheet1!$C$4:$J$4 Lower: refers to: =Sheet1!$B$2 Month: refers to: =Sheet1!$B$1 Table: refers to: =Sheet1!$C$5:$J$16 Upper: refers to: =Sheet1!$B$3 Enter this formula in B4: =SUMPRODUCT(--(INDEX(Table,MONTH(Month),)=Lower),--(INDEX(Table,MONTH(Month),)<=Upper)) That will return the number of values that meet the criteria and act as an error trap check cell. Enter this array formula** in C18 and copy across until you get blanks: =IF(COLUMNS($C18:C18)<=Count,INDEX(Headers,SMALL( IF((INDEX(Table,MONTH(Month),)=Lower)*(INDEX(Tabl e,MONTH(Month),)<=Upper),Array),COLUMNS($C18:C18)) ),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Message posted via http://www.officekb.com |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:85006f85b0ae8@uwe... Hi Biff, That's Brilliant! Thank you very much for all your time and assistance. Most appreciated. Month: refers to: =Sheet1!$B$1 Just in case anyone else views this Thread, my Month in cell B1 was the custom format "mmm", so I referenced another cell that used the full date format to get the month serial number to accomplish MONTH(Month). Cheers, Sam T. Valko wrote: Based on your posted sample data... I used several defined names...(use your actual sheet name) Array: refers to: =COLUMN(Table)-MIN(COLUMN(Table))+1 Count: refers to: =Sheet1!$B$4 Headers: refers to: =Sheet1!$C$4:$J$4 Lower: refers to: =Sheet1!$B$2 Month: refers to: =Sheet1!$B$1 Table: refers to: =Sheet1!$C$5:$J$16 Upper: refers to: =Sheet1!$B$3 Enter this formula in B4: =SUMPRODUCT(--(INDEX(Table,MONTH(Month),)=Lower),--(INDEX(Table,MONTH(Month),)<=Upper)) That will return the number of values that meet the criteria and act as an error trap check cell. Enter this array formula** in C18 and copy across until you get blanks: =IF(COLUMNS($C18:C18)<=Count,INDEX(Headers,SMALL (IF((INDEX(Table,MONTH(Month),)=Lower)*(INDEX(Tab le,MONTH(Month),)<=Upper),Array),COLUMNS($C18:C18) )),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Message posted via http://www.officekb.com |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Another play which delivers it, using non-array formulas
Illustrated in this sample: http://www.freefilehosting.net/download/3i0eg Match Multiple Criteria Horiz.xls In C20: =IF(AND(OFFSET(C4,MATCH($B$1,$B$5:$B$16,0),)=$B2, OFFSET(C4,MATCH($B$1,$B$5:$B$16,0),)<=$B3),COLUMNS ($A:A),"") In C21: =IF(COLUMNS($A:A)COUNT($C$20:$J$20),"",INDEX($C$4 :$J$4,SMALL($C$20:$J$20,COLUMNS($A:A)))) Select C20:C21, copy across to J21. Minimize/hide row20. In C21 across will be returned the required results which satisfy the criteria in B1:B3, all neatly bunched to the left. Adapt to suit the extent of your data -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sam via OfficeKB.com" <u4102@uwe wrote in message news:84fcea97b219e@uwe... I would like a flexible formula that can match multiple criteria and return the results across a single row in consecutive cells. Data Location: Criteria: Month (custom format "mmm") housed in cell B1 Criteria: Values between Lower & Upper Limit (dynamic, will vary). Lower Limit housed in B2, Upper Limit housed in B3. Criteria Limits example: Lower =120, Upper <=150 Return Results: Numeric Labels housed in cells / row C4:BM4 Data: Months (custom format "mmm") Jan to Dec housed in cells / column B5:B16 Data: Numeric Values housed in cells C5:BM16 Scenario: The month criteria housed in cell B1 tells me which ROW of data (within C5: BM16) should have the criteria Lower & Upper Limits applied. The values of the relevant ROW (within C5:BM16) that fulfill the criteria should then have their corresponding Numeric Labels (housed in C4:BM4) returned across a sinlge row in consecutive cells. Sample Data Layout: (8 columns, 12 rows: Jan to Dec) May Criteria Month 120 Lower Limit 150 Upper Limit Labels 1 2 3 4 5 6 7 8 Jan 123 180 165 165 180 119 145 180 Feb 165 119 150 150 119 165 123 100 Mar 119 145 165 150 170 119 170 170 Apr 119 165 119 123 150 145 180 170 May 180 150 165 165 145 150 150 180 Jun 150 170 112 145 145 123 11 145 Jul 150 170 119 170 123 165 150 123 Aug 165 123 170 119 180 119 123 11 Sep 145 165 170 145 145 150 170 150 Oct 150 170 165 150 145 180 180 123 Nov 123 119 145 165 150 119 112 180 Dec 123 150 112 11 145 165 180 119 Expected Results: Numeric Labels (housed in C4:BM4) 2, 5, 6, 7. Based on the criteria Month in cell B1, the Lower & Upper Limits should be applied to row 5 of my data, which corresponds to the month of May. Looking for values in May (row 5) that meet criteria of =120 (greater than or equal to 120) and <=150 (less than or equal to 150); then return their corresponding labels: 2, 5, 6, 7. Thanks Sam P.S., Tried to use the MATCH(1,(criteria)*(criteria)) etc. in some of my attempts! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200805/1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Hi Max,
This is Great! Very much appreciate seeing your version. Thank you very much for your time and assistance. Max wrote: Another play which delivers it, using non-array formulas Illustrated in this sample: http://www.freefilehosting.net/download/3i0eg Match Multiple Criteria Horiz.xls Couldn't download above file at http://www.freefilehosting.net/download/3i0eg or at http://savefile.com/projects/236895. Got a bit side tracked with all your excellent sample files. In C20: =IF(AND(OFFSET(C4,MATCH($B$1,$B$5:$B$16,0),)=$B2 ,OFFSET(C4,MATCH($B$1,$B$5:$B$16,0),)<=$B3),COLUMN S($A:A),"") In C21: =IF(COLUMNS($A:A)COUNT($C$20:$J$20),"",INDEX($C$ 4:$J$4,SMALL($C$20:$J$20,COLUMNS($A:A)))) Select C20:C21, copy across to J21. Minimize/hide row20. In C21 across will be returned the required results which satisfy the criteria in B1:B3, all neatly bunched to the left. Adapt to suit the extent of your data -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Cheers, Sam -- Message posted via http://www.officekb.com |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Welcome, the earlier link seems to work ok for me (just tested)
Anyway, here's an alternative link to the same sample: http://www.savefile.com/files/1584851 Match Multiple Criteria Horiz.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sam via OfficeKB.com" <u4102@uwe wrote in message news:8500f3cf07e83@uwe... Hi Max, This is Great! Very much appreciate seeing your version. Thank you very much for your time and assistance. Couldn't download above file at http://www.freefilehosting.net/download/3i0eg or at http://savefile.com/projects/236895. Got a bit side tracked with all your excellent sample files. Cheers, Sam |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Hi Max,
Downloaded file ok using link below. Thanks again. Great sample files. Cheers, Sam Max wrote: Welcome, the earlier link seems to work ok for me (just tested) Anyway, here's an alternative link to the same sample: http://www.savefile.com/files/1584851 Match Multiple Criteria Horiz.xls -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200806/1 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Multiple Criteria & Return Numeric Labels across single row
Welcome, Sam.
Thanks for feeding back -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sam via OfficeKB.com" <u4102@uwe wrote in message news:8507cef0acc8c@uwe... Hi Max, Downloaded file ok using link below. Thanks again. Great sample files. Cheers, Sam |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Matched Numeric Labels across Single Row | Excel Worksheet Functions | |||
| Return Numeric Labels that have Values =4 across Single Row | Excel Worksheet Functions | |||
| 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 | |||
Hybrid Mode
