Index Match Help needed

Hi Dears,

Hope you are doing well,

May I ask you some help to adapt my Index/match formula please ?

I am trying to retreave some data's from the sheet pivot FCST, based on 6 different criterias, Cust group/Brand,Format,bottle type, Month and year,

But only for the months 10 to 12

Thanks in advance,

In that excel, customer group how you match?

I am sorry,

I pasted the table in the wrong way (missing a column), here-below you will find the correct one

In O5, try this:

=SUMIFS('Pivot FCST'!E:E,'Pivot FCST'!$A:$A,$B5,'Pivot FCST'!$B:$B,$C5,'Pivot FCST'!$C:$C,$D5,'Pivot FCST'!$D:$D,$E5)

Copy to the right and down.

Hi,

Would it be possible to have this formula more dynamic ? taking into account Month number and Year number?

I have tried to add it in your formula but i have a value error return,

Thanks in advance !

Based on how your data is set up in your example, it doesn't need to look at month or year number.

The INDEX portion of the function moves over one column for every column that the formula is dragged over. There is no need to complicate it.

If this formula isn't working as you expect on your actual spreadsheet, perhaps you should create a small representative sample of your data along with the desired result/s of the formula/s.

Hi,

I agree with you,

For example, for the time being you have the month october which is in column E:E in the sheet pivot FCST,

But If i am updating the file in the future, let's say November, October won't be present in the Pivot FCST sheet anymore and replaced by the month november,
That means that the column November in my sheet overview will return a value from month December in the Pivot FCST sheet (because it is not dynamic enough)

hope it is clear let me know,

Thanks in advance

So in my overview sheet, the formula in month november will return a value from month

In O5, try this:

=SUMPRODUCT(('Pivot FCST'!$A$5:$A$695=$B5)*('Pivot FCST'!$B$5:$B$695=$C5)*('Pivot FCST'!$C$5:$C$695=$D5)*('Pivot FCST'!$D$5:$D$695=$E5)*('Pivot FCST'!$E$1:$V$1=O$2)*('Pivot FCST'!$E$2:$V$2=$D$1)*('Pivot FCST'!$E$5:$V$695))

Note that the calculations are set to manual in the workbook that you shared.

If you want to use INDEX MATCH, you can use this:

O5 =INDEX('Pivot FCST'!$E$5:$V$900,MATCH(1,('Pivot FCST'!$A$5:$A$900=$B5)*('Pivot FCST'!$B$5:$B$900=$C5)*('Pivot FCST'!$C$5:$C$900=$D5)*('Pivot FCST'!$D$5:$D$900=$E5),0),MATCH(1,('Pivot FCST'!$E$1:$V$1=O$2)*('Pivot FCST'!$E$2:$V$2=$D$1),0)) Ctrl Shift Enter

or this (non-array version):

O5 =INDEX('Pivot FCST'!$E$5:$V$900,MATCH(1,INDEX(('Pivot FCST'!$A$5:$A$900=$B5)*('Pivot FCST'!$B$5:$B$900=$C5)*('Pivot FCST'!$C$5:$C$900=$D5)*('Pivot FCST'!$D$5:$D$900=$E5),0),0),MATCH(1,INDEX(('Pivot FCST'!$E$1:$V$1=O$2)*('Pivot FCST'!$E$2:$V$2=$D$1),0),0))