Index and Match for multiple criteria

[Edgie]

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

 

[ArthriticPanda]

Hi Matt,

Is this any use:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	*	Year	Month	Date	ID	Sales	Value	Profit	*
2	*	2013	March	10313	Ben	100	200	75	*
3	*	*	*	*	James	50	200	23	*
4	*	*	*	*	Paul	75	40	1	*
5	*	*	*	20313	Ben	200	200	50	*
6	*	*	*	*	James	150	120	50	*
7	*	*	*	*	Paul	40	25	15	*
8	*	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	ID	Date	Profit
13	*	*	*	*	*	*	Ben	10313	75
14	*	*	*	*	*	*	James	*	23
15	*	*	*	*	*	*	Paul	*	1
16	*	*	*	*	*	*	Ben	20313	50
17	*	*	*	*	*	*	James	*	23
18	*	*	*	*	*	*	Paul	*	1
Sheet1

AP

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

 

[Edgie]

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

 

[ArthriticPanda]

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]

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]

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
	A	B	C	D	E	F	G	H	I	J
1	*	01/03/2013	168,257	01/03/2013	93,382	*	*	*	*	*
2	*		168,257	04/03/2013	130,315	*	*	*	*	*
3	*		168,257	05/03/2013	115,272	*	*	*	*	*
4	*	04/03/2013	55,141	01/03/2013	30,604	*	*	*	*	*
5	*		55,141	04/03/2013	42,706	*	*	*	*	*
6	*		55,141	05/03/2013	37,777	*	*	*	*	*
7	*	05/03/2013	35,132	01/03/2013	19,498	*	*	*	*	*
8	*		35,132	04/03/2013	27,209	*	*	*	*	*
9	*		35,132	05/03/2013	24,068	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	Date	Sales Volume	Prices	Sales Value	*
13	*	*	*	*	*	01/03/2013	168,257	01/03/2013	93,382	*
14	*	*	*	*	*	04/03/2013	55,141	04/03/2013	42,706	*
15	*	*	*	*	*	05/03/2013	35,132	05/03/2013	24,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