Creating a formula to Search data set and return specific number

TexasTea

New Member
Joined
Apr 17, 2014
Messages
4
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:
DollarsDollarsUnitsUnits
CategoriesMonthsCustomer 1MarketCustomer 1Market
Cat 1Jan10,00050,0002,00012,000
Cat 1Feb20,00060,0004,00014,000
Cat 1Mar30,00070,0006,00016,000
Cat 2Jan5,00030,0001,00011,000
Cat 2Feb15,00035,0003,00013,000
Cat 2Mar25,00040,0005,00015,000
Cat 3Jan5,00010,0006,00011,000
Cat 3
Feb30,00035,0003,00012,000
Cat 3Mar5,00050,0006,00014,000
Cat 4Jan5,00060,0001,00012,000
Cat 4Feb10,00030,0001,00011,000
Cat 4Mar40,00070,0003,00016,000

<tbody>
</tbody>


Table:

CategoryMonthCustomer 1 DollarsCustomer 1 Units
Cat 1Mar(return the number 30,000)?
Cat 1Feb??
Cat 2Mar??
Cat 2Feb??

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,420
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,952
Messages
5,599,033
Members
414,275
Latest member
Pungie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top