INDEX MATCH with multiple criteria - looking for all dates in month/range - please help!!

[thelittleredfox]

Hi,

I'm really stuck on this one. Is it possible to do the following with a single formula?

I have two sheets in my workbook, the one with the formula on is called 'summary' and the other 'raw data'.

The sheets contain data on insurance policies. Each policy has a unique number which appears in column A. Each policy should pay a commission sum each month, so there should be one line for each policy in the date range for each month. The Raw data sheet simply lists all payments on all policies, which appear in date order, see below:

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]Client name
[/TD]
[TD]Policy number
[/TD]
[TD]Commission
[/TD]
[/TR]
[TR]
[TD]20/12/2014
[/TD]
[TD]Sarah Webb
[/TD]
[TD]GS348591
[/TD]
[TD]£4.82
[/TD]
[/TR]
</TBODY>[/TABLE]


On the summary sheet, I have one line for each policy number (policy number appears in column A). Going across the spreadsheet, there is a column for each month.
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]Policy Number
[/TD]
[TD]August 14
[/TD]
[TD]September 14
[/TD]
[TD]October 14
[/TD]
[/TR]
[TR]
[TD]L8008423
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS348591
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B234955F
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]









I need my formula to look on the Raw data sheet for the policy number in column A of the summary sheet, then look in the date column in the Raw data sheet for the payment that falls within the month at the top of the column in the summary sheet. Where both these criteria are true, I need it to find the relevant commission amount in column G in the Raw data sheet.

Other info:
Currently, I have got the workbook doing what I want it to by inserting a helper column in column B of the Raw data sheet which uses MONTH to calculate the month of each date in column A. I then inserted the MONTH formula into cell row 1 of the summary sheet, to give the numeric month value (e.g. 8 for August 14) of the headings in row 2. Using this formula:


{=IFERROR(INDEX('Raw data'!$M$4:$M$1500,MATCH(1,('Raw data'!$G$4:$G$1500=$A4)*('Raw data'!$B$4:$B$1500=F$1),0)),"")}

I was able to get the result I wanted. But is there a simple way? The raw data sheet has new data copied and pasted into it each month, so ideally I don't want to have to have a helper column there.

Is there some way to use MONTH as part of the criteria in the INDEX MATCH array formula? Please help, I've probably spent a couple of days trying to work this out!

 

[Weazel]

maybe something like...

Excel 2012[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Date[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00"]Client name[/TD]
[TD="bgcolor: #FFFF00"]Policy number[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00"]Commission[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10/10/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Sarah Webb[/TD]
[TD]B234955F[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]$4.82[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]12/5/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Joe Smith[/TD]
[TD="bgcolor: #FAFAFA"]GS348591[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]$5,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFF00"]Policy Number[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]August 14[/TD]
[TD="bgcolor: #FFFF00, align: right"]September 14[/TD]
[TD="bgcolor: #FFFF00, align: right"]October 14[/TD]
[TD="bgcolor: #FFFF00, align: right"]November 14[/TD]
[TD="bgcolor: #FFFF00, align: right"]December 14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]L8008423[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]GS348591[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$5,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]B234955F[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$4.82[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Raw Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F6[/TH]
[TD="align: left"]{=IFERROR(INDEX('Raw Data'!$M$2:$M$3,MATCH($A6,IF(MONTH('Raw Data'!$A$2:$A$3)=MONTH(F$5),'Raw Data'!$G$2:$G$3),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[delos001]

I would pivot the raw data and then use a getpivot formula to get the commission data you are looking for based on policy number and date:

=GETPIVOTDATA("Commission",$G$1,"Date",MONTH($F$1),"Date",YEAR($F$1),"Policy number",A2)

You will want to put the dates in the Column Labels field and group by month and year.

Where G1 is the anchor for the pivot table
F1 is the cell with the date you want to look up
A2 is the policy number.

Each time you drop a new raw data field in, update the pivot table, and refresh the formulas on your summary sheet.

To be honest, I am not sure you even need the getpivot formula. Unless Im not understanding, the pivot table will put it exactly how you need it each time. You can also get sums, averages, etc. And double clicking on a data point within the pivot table will create a new table with the exact raw data.

 

[thelittleredfox]

Thanks, Weazel! I feel like such an idiot now!

I'd been playing around with something similar:
{=IFERROR(INDEX('Raw data'!$M$4:$M$1500,MATCH($A4,IF(MONTH('Raw data'!$A$4:$A$1500)=$F$1,'Raw data'!$G$4:$G$1500),0)),""0}<o></o>

but couldn't get the MONTH bit to work and realised my expression of it was wrong. Now it does work, that is great! It was so simple, I can't believe I couldn't do it!

 

[Weazel]

Glad you got it working