INDEX MATCH Error with multiple criteria

[A819A1L]

Hello,

I'm trying to pull through data with multiple criteria using the INDEX MATCH functions but I'm getting this error:

"Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated"

The formula I'm using in cell B3 "Chart Data" tab (attached) is: 

=INDEX(February!A1:AQ47,MATCH((February!AA2=February!2:2)*('Chart Data'!A3=February!3:3)*(B2=February!A:A),0)).

The cell should return 83%, criteria being "Client 1", "wk 5", "Activated%".

I've tried specifying the range when referencing "February" tab, but it returns the SPILL! error.  Any help would be appreciated. I'm working with Excel 2016 in Mac OS.

Cheers,

[#2]

@A819A1L 

First of all, I assume you want to populate B7 (Client 1, wk5), not B3 (Client 1, wk 1).

One of your problems is that you have 4 times a column header "wk 5", "wk 6" etc. in the February sheet. You need to make the headers unique if you want to be able to match them. Then you may have the same client in every "KA-section", although I don't really understand what these sections stand for. So, you can't can't index/match the same client in multiple sections in one formula. Thus, separate each "KA-section". 

In the attached workbook, I have named the "wk 5" column with the 83% to "wk 5 %" and I have added the % to each row header in the Chart Data. The formula in B7 returns the desired 83%.

[#3]

HI @Riny_van_Eekelen thanks for your input.

Your assumption is right, I was trying to create a formula that would return a value based on the client name and week.  So, specifically B7 would return the 83% not B3, my mistake.  The clients names are different for each KA (Key Account) category going forward so there's no issue there, I was just putting in names for arguments sake.

I was trying to see if there's a way to return a value that has two column heading criteria i.e. "wk 5" and "Activated%" so that I would be able to keep the 4 column "wk 5" headers the same, but your solution is much simpler. 

Thank you!