Index Match Date and Month

[SAXON10]

Hi,

How to index match with date and month ranges. I am looking for two different output based on the following data.

1. Date wise

2.Month wise

I apply the following formula in I3==IF(MATCH($G$3,$A$3:$A$1000,0),INDEX(DATA!$C$3:$E$1000,MATCH($H3,DATA!$B$3:$B$1000,0),MATCH(I$2,DATA!

$C$2:$E$2,0))) but I got wrong output.

Data Range:A2:A11 and Output Range:G2:K10

https://www.dropbox.com/s/cfeq8vfcr9pvaq6/INMA.PNG?dl=0

Any help much appreciated.


[TABLE="width: 1118"]
<colgroup><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 5"]DATA[/TD]
[TD][/TD]
[TD="colspan: 5"]OUTPUT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]COUNTRY[/TD]
[TD]APPLE[/TD]
[TD]BANANA[/TD]
[TD]BILBERRY[/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD]COUNTRY[/TD]
[TD]APPLE[/TD]
[TD]BANANA[/TD]
[TD]BILBERRY[/TD]
[TD]DATE WISE[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]UK[/TD]
[TD]1[/TD]
[TD]58[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]02/01/2018[/TD]
[TD]UK[/TD]
[TD]1[/TD]
[TD]58[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]US[/TD]
[TD]5[/TD]
[TD]48[/TD]
[TD]24[/TD]
[TD][/TD]
[TD] [/TD]
[TD]US[/TD]
[TD]5[/TD]
[TD]48[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]EU[/TD]
[TD]78[/TD]
[TD]44[/TD]
[TD]446[/TD]
[TD][/TD]
[TD] [/TD]
[TD]EU[/TD]
[TD]78[/TD]
[TD]44[/TD]
[TD]446[/TD]
[/TR]
[TR]
[TD]02/01/2018[/TD]
[TD]UK[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]US[/TD]
[TD]21[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]DATE[/TD]
[TD]COUNTRY[/TD]
[TD]APPLE[/TD]
[TD]BANANA[/TD]
[TD]BILBERRY[/TD]
[TD]MONTHWISE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]EU[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]1515[/TD]
[TD][/TD]
[TD]Jan-18[/TD]
[TD]UK[/TD]
[TD]50[/TD]
[TD]107[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]03/01/2018[/TD]
[TD]UK[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]US[/TD]
[TD]27[/TD]
[TD]70[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]US[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD] [/TD]
[TD]EU[/TD]
[TD]90[/TD]
[TD]56[/TD]
[TD]1962[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]EU[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Misca]

If your data had the dates on every row, it would be really easy to solve both your problems with SUMPRODUCT or SUMIFS.

As it is, you should get the daily figures with

=INDEX(C$3:C$11,MATCH($G$3,$A$3:$A$11,0)+MATCH($H3,$H$3:$H$5,0)-1)

If your products in Data table were in different order than in your Output table, you'd have to use MATCH to find the right column as well but since they're in the same order I've left that part out to make the formula easier to read / understand.

The first MATCH in the formula finds the right date row but since there's also the Country column you need to add another MATCH for the country as well. And since the Country match returns 1 for UK and UK in your data is on the same row as your date, you need to subtract 1 from the second MATCH.

The monthly figures with empty rows in the data are much harder to calculate. A simple

=SUMPRODUCT(--(MONTH($A$3:$A$11)=MONTH($G$8)),--($B$3:$B$11=$H8),C$3:C$11)

returns correct numbers for January but only because the empty cells return 1 for the month number (Day 0 in Excel stands for January 0 1900 [or 1904, if you happen to be using the 1904 date system]).

If there's a row for each date - especially for the first date of each month - something like

=SUMPRODUCT(--(ROW($A$3:$A$11)>=ROW($A$2)+MATCH($G$8,$A$3:$A$11,0)),--(ROW($A$3:$A$11)<row($a$2)+match(eomonth($g$8,0)+1,$a$3:$a$11)),--($b$3:$b$11=$h8),c$3:c$11)


should work. G8 in this formula is supposed to always be the first of month and unlike the INDEX / MATCH solution this one can handle the year as well (the INDEX / MATCH only looked for the MONTH).

The first part of the formula makes sure the ROW is greater or equal to the first match of the dates (+ the header row unless you start your match from the first row of your worksheet as well). The second part makes sure the ROW is less than the MATCH for February 1st. The rest of the formula is just making sure the Country matches etc.</row($a$2)+match(eomonth($g$8,0)+1,$a$3:$a$11)),--($b$3:$b$11=$h8),c$3:c$11)

 

[SAXON10]

Much appreciated for your help.

Thanks for considered my thread and clarify index match function. I follow-up your suggestion and formulas are working well.
​

You are legend.
​