Creating a formula to Search data set and return specific number

[TexasTea]

I have the below data set and I need that information to complete the below table. The below data set is a small example, where you see Cat 1, Cat 2, etc - there are actually 30 of these.
I need to fill in the below table, and need help creating the correct index, match or offset formula to complete this.

Thanks for any help you can provide.

The Data Set:

		Dollars	Dollars	Units	Units
Categories	Months	Customer 1	Market	Customer 1	Market
Cat 1	Jan	10,000	50,000	2,000	12,000
Cat 1	Feb	20,000	60,000	4,000	14,000
Cat 1	Mar	30,000	70,000	6,000	16,000
Cat 2	Jan	5,000	30,000	1,000	11,000
Cat 2	Feb	15,000	35,000	3,000	13,000
Cat 2	Mar	25,000	40,000	5,000	15,000
Cat 3	Jan	5,000	10,000	6,000	11,000
Cat 3	Feb	30,000	35,000	3,000	12,000
Cat 3	Mar	5,000	50,000	6,000	14,000
Cat 4	Jan	5,000	60,000	1,000	12,000
Cat 4	Feb	10,000	30,000	1,000	11,000
Cat 4	Mar	40,000	70,000	3,000	16,000

<tbody>
</tbody>

Table:

Category	Month	Customer 1 Dollars	Customer 1 Units
Cat 1	Mar	(return the number 30,000)	?
Cat 1	Feb	?	?
Cat 2	Mar	?	?
Cat 2	Feb	?	?

<tbody>
</tbody>

 

[jtakw]

Hi,

This is strictly based on your sample, will probably need modifications since there are no provisions for Customer 2, 3, etc.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dollars</td><td style=";">Dollars</td><td style=";">Units</td><td style=";">Units</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Categories</td><td style=";">Months</td><td style=";">Customer 1</td><td style=";">Market</td><td style=";">Customer 1</td><td style=";">Market</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Cat 1</td><td style=";">Jan</td><td style="text-align: right;;">10,000</td><td style="text-align: right;;">50,000</td><td style="text-align: right;;">2,000</td><td style="text-align: right;;">12,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Cat 1</td><td style=";">Feb</td><td style="text-align: right;;">20,000</td><td style="text-align: right;;">60,000</td><td style="text-align: right;;">4,000</td><td style="text-align: right;;">14,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Cat 1</td><td style=";">Mar</td><td style="text-align: right;;">30,000</td><td style="text-align: right;;">70,000</td><td style="text-align: right;;">6,000</td><td style="text-align: right;;">16,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Cat 2</td><td style=";">Jan</td><td style="text-align: right;;">5,000</td><td style="text-align: right;;">30,000</td><td style="text-align: right;;">1,000</td><td style="text-align: right;;">11,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Cat 2</td><td style=";">Feb</td><td style="text-align: right;;">15,000</td><td style="text-align: right;;">35,000</td><td style="text-align: right;;">3,000</td><td style="text-align: right;;">13,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Cat 2</td><td style=";">Mar</td><td style="text-align: right;;">25,000</td><td style="text-align: right;;">40,000</td><td style="text-align: right;;">5,000</td><td style="text-align: right;;">15,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Cat 3</td><td style=";">Jan</td><td style="text-align: right;;">5,000</td><td style="text-align: right;;">10,000</td><td style="text-align: right;;">6,000</td><td style="text-align: right;;">11,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Cat 3</td><td style=";">Feb</td><td style="text-align: right;;">30,000</td><td style="text-align: right;;">35,000</td><td style="text-align: right;;">3,000</td><td style="text-align: right;;">12,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Cat 3</td><td style=";">Mar</td><td style="text-align: right;;">5,000</td><td style="text-align: right;;">50,000</td><td style="text-align: right;;">6,000</td><td style="text-align: right;;">14,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Cat 4</td><td style=";">Jan</td><td style="text-align: right;;">5,000</td><td style="text-align: right;;">60,000</td><td style="text-align: right;;">1,000</td><td style="text-align: right;;">12,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Cat 4</td><td style=";">Feb</td><td style="text-align: right;;">10,000</td><td style="text-align: right;;">30,000</td><td style="text-align: right;;">1,000</td><td style="text-align: right;;">11,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Cat 4</td><td style=";">Mar</td><td style="text-align: right;;">40,000</td><td style="text-align: right;;">70,000</td><td style="text-align: right;;">3,000</td><td style="text-align: right;;">16,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Table:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dollars</td><td style=";">Units</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Category</td><td style=";">Month</td><td style=";">Customer 1</td><td style=";">Customer 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Cat 1</td><td style=";">Mar</td><td style="text-align: right;;">30000</td><td style="text-align: right;;">6000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Cat 1</td><td style=";">Feb</td><td style="text-align: right;;">20000</td><td style="text-align: right;;">4000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Cat 2</td><td style=";">Mar</td><td style="text-align: right;;">25000</td><td style="text-align: right;;">5000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">Cat 2</td><td style=";">Feb</td><td style="text-align: right;;">15000</td><td style="text-align: right;;">3000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet10</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C20</th><td style="text-align:left">=IF(<font color="Blue">C$2=C$19,SUMIFS(<font color="Red">C$3:C$14,A$3:A$14,A20,B$3:B$14,B20</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D20</th><td style="text-align:left">=IF(<font color="Blue">E$2=D$19,SUMIFS(<font color="Red">E$3:E$14,A$3:A$14,A20,B$3:B$14,B20</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

C20 and D20 formulae copied down.