Index & Match with 3 matches

[pauldaddyadams]

Hi,

Can anyone help with a formula with three lookups and then a cumulative formula...

On the tabs "111 Inverurie" and "145 Belfast" I have cells in which I need formula in to return data from the "Export" sheet.

My problem is that due to the way the data is exported I need three lookups, one to match the branch, KPI and month.

The matches are.

• Cell A1 on "111 Inverurie" Sheet with Column A on the "Export" sheet
• Row 2 on "111 Inverurie" Sheet with Column B on the "Export" sheet
• Cell A3:A5 on "111 Inverurie" Sheet with Row 2 on the "Export" sheet

Can this 3 way look up be achieved?


Sumproduct Formula 2:
Then the added complication is that I also need a year to date formula (column D on the branch tabs) which will do the above but rather than pull one months worth of data it would pull the cumulative total. e.g. if Feb is selected then it would sum Jan and Feb.

Can anyone help with either formula please?


Regards

Paul

[zbor]

There is no such shets in example workbook

[XOR LX]

Hi,

There is only one sheet in that attachment: "Dashboard 2014"

Regards

[pauldaddyadams]

Sorry - uploaded the wrong file. It is now updated in the original post

[pauldaddyadams]

Is there anyway I can have a three way lookup with this layout?

[zbor]

What you want to return as result?

[pauldaddyadams]

I have updated the spreadsheet to show the expected output based on the month of February.

The month columns should contain the month data and the YTD column sums Jan and Feb month data.

I have linked the cells to show where it should pick the data up from

[pauldaddyadams]

Hi,

Managed to do it through:
=SUMPRODUCT((Export!$A$3:$A$30=$A$1)*(Export!$B$3:$B$30=$B$2)*(Export!$C$2:$E$2=A3)*(Export!$C$3:$E$30))
and
=SUMPRODUCT((Export!$A$3:$A$30=$A$1)*(Export!$B$3:$B$30<=$B$2)*(Export!$C$2:$E$2=A3)*(Export!$C$3:$E$30))