Match Multiple Criteria & Return Numeric Labels across single row

[Sam via OfficeKB.com]

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!

 

[T. Valko]

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(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)

 

[Max]

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

 

[Sam via OfficeKB.com]

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

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

 

[Sam via OfficeKB.com]

Hi Max,

This is Great! Very much appreciate seeing your version.
Thank you very much for your time and assistance.

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),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

Cheers,
Sam

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Max]

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

 

[Sam via OfficeKB.com]

Hi Max,

Downloaded file ok using link below.
Thanks again. Great sample files.

Cheers,
Sam

 

[Max]

Welcome, Sam.
Thanks for feeding back