Index and Match for multiple criteria

Edgie

Board Regular
Joined
Aug 5, 2011
Messages
102
Hi,

I have a table of data and trying to return a cell value after matching certain criteria (multiple).

I have tried an index and match formula however it isn't working.

The a table is a pivot linked to a database so i can't change any of the data or format it.

Pivot Data:
Year
Month
Date
ID
Sales
Value
Profit
2013
March
10313
Ben
100
200
75
James
50
200
23
Paul
75
40
1
20313
Ben
200
200
50
James
150
120
50
Paul
40
25
15

<TBODY>
</TBODY>


Desired Outcome<STYLE type=text/css>
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</STYLE>
Id
Date
Profit
Ben
10313
75
James
10313
23
Paul
10313
1

<TBODY>
</TBODY>

Now the issue i have is that the date isn't in every cell in column C in the pivot table, only when there is a new date so when i use an index and match the results for "Ben" will always show because there is a date next to that column, however all other results won't show.

The formual and desired outcome was on another tab and using named ranges:

=IFERROR(INDEX(Profit,MATCH(1,(A2=ID)*(B2=Dates),0)),0)

Thanks

Matt
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

ArthriticPanda

Active Member
Joined
Feb 24, 2013
Messages
292
Hi Matt,

Is this any use:


Excel Workbook
ABCDEFGHI
1*YearMonthDateIDSalesValueProfit*
2*2013March10313Ben10020075*
3****James5020023*
4****Paul75401*
5***20313Ben20020050*
6****James15012050*
7****Paul402515*
8*********
9*********
10*********
11*********
12******IDDateProfit
13******Ben1031375
14******James*23
15******Paul*1
16******Ben2031350
17******James*23
18******Paul*1
Sheet1



AP

PS I've set the sheet not to display zero values in Options.
 
Last edited:

Edgie

Board Regular
Joined
Aug 5, 2011
Messages
102
Hi,

Thanks very much for your response....in my effort to simplify the example i got it wrong so apologies.

Here is the actual example and the issues i am having.

Date Sales Volume Prices Sales Value
20130301 168,257 Prices 010313 93,382
168,257 Prices 040313 130,315
168,257 Prices 050313 115,272
20130304 55,141 Prices 010313 30,604
55,141 Prices 040313 42,706
55,141 Prices 050313 37,777
20130305 35,132 Prices 010313 19,498
35,132 Prices 040313 27,209
35,132 Prices 050313 24,068
Outcome
Date Sales Volume Prices Sales Value
20130301 168,257 Prices 010313 93,382
20130304 55,141 Prices 040313 42,706
20130305 35,132 Prices 050313 24,068

<TBODY>
</TBODY>


<STYLE type=text/css>
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</STYLE>Pivot:
The date in Column A populates the sales volume in B and you have to select all the prices which populates the sales Value. The pivot, or database doesn't know that the prices corresponds to a date in column A. Also, when you select the multiple prices, it replicates the sales volume in column B for each cell as shown in the example above.

Now obviously what i could do is select each date and the corresponding prices, copy the data then do another but it is for a large data source.

The outcome table, i can populate the date and prices columns and i can do a lookup to bring back the sales volume...but it is the Sales Value which is proving difficult and i think it is because there isn't a date in every cell in column A, it only states the date once.

Is there anyway to do this?

Thanks
 
Last edited:

ArthriticPanda

Active Member
Joined
Feb 24, 2013
Messages
292
Sorry Matt, I totally misread the fact you were pulling the data from a pivot table (even though you did mention it).

Is there some reason why the date is blank in your data source?

Is this the outcome you are looking for?

Date
Sales Volume
Prices
Sales Value
20130301
168,257
Prices 010313
93,382
20130301
168,257
Prices 040313
130,315
20130301
168,257
Prices 050313
115,272

<tbody>
</tbody>
 

Edgie

Board Regular
Joined
Aug 5, 2011
Messages
102
Hi, thanks for the response.

Unfortunately i cannot change the data so the date doesn't appear in every cell, it is just a summary of the date. So everytime there is a new date it is just in the first new row and in order.

If the date was in every cell then i could do an easy sum product.

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Date</th><th>Sales Volume</th><th>Prices</th><th>Sales Value</th></tr>
<tr><td>01/03/2013</td><td>168,257</td><td>01/03/2013</td><td>93,382</td></tr>
<tr><td> </td><td>168,257</td><td>04/03/2013</td><td>130,315</td></tr>
<tr><td> </td><td>168,257</td><td>05/03/2013</td><td>115,272</td></tr>
<tr><td>04/03/2013</td><td>55,141</td><td>01/03/2013</td><td>30,604</td></tr>
<tr><td> </td><td>55,141</td><td>04/03/2013</td><td>42,706</td></tr>
<tr><td> </td><td>55,141</td><td>05/03/2013</td><td>37,777</td></tr>
<tr><td>05/03/2013</td><td>35,132</td><td>01/03/2013</td><td>19,498</td></tr>
<tr><td> </td><td>35,132</td><td>04/03/2013</td><td>27,209</td></tr>
<tr><td> </td><td>35,132</td><td>05/03/2013</td><td>24,068</td></tr>
<tr><td> </td><td> </td><td> </td><td> </td></tr>
<tr><td>Outcome</td><td> </td><td> </td><td> </td></tr>
<tr><td>Date</td><td>Sales Volume</td><td>Prices</td><td>Sales Value</td></tr>
<tr><td>01/03/2013</td><td>168,257</td><td>01/03/2013</td><td>93,382</td></tr>
<tr><td>04/03/2013</td><td>55,141</td><td>04/03/2013</td><td>42,706</td></tr>
<tr><td>05/03/2013</td><td>35,132</td><td>05/03/2013</td><td>24,068</td></tr></table>

Here is a better example. All i am after is the correct Sales value, and it gathers that by looking for the date in column A and the corresponding date in Prices, column C. I can populate the outcome table, just need the formula for Sales Value.
 

ArthriticPanda

Active Member
Joined
Feb 24, 2013
Messages
292
Matt,

I think I might have it...fingers crossed! Your last example helped me a lot. The drawback being you need to kick it off by typing in the date and it populates the rest based on the date you type in.

Excel Workbook
ABCDEFGHIJ
1*01/03/2013168,25701/03/201393,382*****
2*168,25704/03/2013130,315*****
3*168,25705/03/2013115,272*****
4*04/03/201355,14101/03/201330,604*****
5*55,14104/03/201342,706*****
6*55,14105/03/201337,777*****
7*05/03/201335,13201/03/201319,498*****
8*35,13204/03/201327,209*****
9*35,13205/03/201324,068*****
10**********
11**********
12*****DateSales VolumePricesSales Value*
13*****01/03/2013168,25701/03/201393,382*
14*****04/03/201355,14104/03/201342,706*
15*****05/03/201335,13205/03/201324,068*
16**********
Sheet1





Hopefully this will be of use.

PS I was also thinking...could you obtain similar results by re-arranging the fields in your pivot?

AP
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,396
Messages
5,601,420
Members
414,449
Latest member
Pashtun

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