Index Match multiple criteria

[timjohnny]

Hi all,
I hope you're all doing well

I have the following table (this is just dummy data, for all the sports fans who read this ), and I want to get the correct numbers from the table on the left into the correct fields in the table on the right, e.g. G13 into N13. I've found an index match formula with multiple criteria online, i.e. one that let's me specify that I want the 2006 Football wins. I've tried to add another criterion to specify that I want only the Germany ones but I've run into this error and I have a strong suspicion that it's because I don't actually understand the syntax of the formula. Does anyone have any suggestions for how to do this? I need to solve this because I need to be able to change K12 as I wish with the formula already in place, so that I can get whichever country's results whenever I need them.

Thanks so much for any help!!

 

[Fluff]

How about

+Fluff 1.xlsm
	E	F	G	H	I	J	K	L	M	N
12			Germany	UK	Spain		Germany	2006	Football	Wins
13	2006	Football	2	11	4					2
14	2007	Football	3	3	8
15	2006	Hockey	1	5	10
16	2007	Hockey	7	0	2
Main

Cell Formulas
Range	Formula
N13	N13	=INDEX(INDEX(G13:I16,0,MATCH(K12,G12:I12,0)),AGGREGATE(15,6,(ROW(G13:G16)-ROW(G13)+1)/(E13:E16=L12)/(F13:F16=M12),1))

 

[Fluff]

Thinking about it, a simpler formula would be

=SUMPRODUCT((E13:E16=L12)*(F13:F16=M12)*(G12:I12=K12),G13:I16)

 

[steve the fish]

You need to supply your index with two values. One for rows and one for columns. The way you have designed it there you only supply index one value hence it fails. You can use index match match or go with fluffs reply

 

[timjohnny]

Hi Fluff and Steve the fish,

thanks for responding so quickly!

Sorry for responding slowly on my end, I had to figure out which parts of the formula to lock ($) because I had to see if I could drag it correctly Fluff that sumproduct solution works magnificently, thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.