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