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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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:
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,214,360
Messages
6,119,074
Members
448,866
Latest member
CKW USMC

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
Back
Top