Impossible finding solution for table result HLOOKUP,MATCH,INDEX column [SOLVED]

[guatelize]

I've been looking several days for my problem, but no solution found.

My table consist of 1 heading (A1:F1) and 6 columns (A2:F25):

[pre]

Loc	Month	Apple	Banana	Orange	Kiwi

EU	1	11	35	59	83
US	1	12	36	60	84
EU	2	13	37	61	85
US	2	14	38	62	86
EU	3	15	39	63	87
US	3	16	40	64	88
EU	4	17	41	65	89
US	4	18	42	66	90
EU	5	19	43	67	91
US	5	20	44	68	92
EU	6	21	45	69	93
US	6	22	46	70	94
EU	7	23	47	71	95
US	7	24	48	72	96
EU	8	25	49	73	97
US	8	26	50	74	98
EU	9	27	51	75	99
US	9	28	52	76	100
EU	10	29	53	77	101
US	10	30	54	78	102
EU	11	31	55	79	103
US	11	32	56	80	104
EU	12	33	57	81	105
US	12	34	58	82	106

How can I retrieve the amounts as shown below :
Month
Fruit	Loc	1	2	3

Apple	EU	11	13	15
Apple	US	12	14	16
Banana	EU	35	37	39
Banana	US	36	38	40

[/pre]
Thanks for your help.

 

[Faseeh]

With your data in A1:F25, and the resulting report in other sheet in A1:E5, enter this in C2 and drag to E5:

=SUMPRODUCT((Sheet1!$C$1:$F$1=$A2)*(Sheet1!$A$2:$A$25=$B2)*(C$1=Sheet1!$B$2:$B$25)*(Sheet1!$C$2:$F$25))

Regards,

 

[Faseeh]

Try these as well, with similar data layouts:

=HLOOKUP($H2,$B$1:$F$25,MATCH($I2&J$1,$A$2:$A$25&$B$2:$B$25,0)+1,FALSE) Press Ctrl+Shift+Enter to execute.


or.


=HLOOKUP($H2,$B$1:$F$25,SUMPRODUCT(($I2=$A$2:$A$25)*(J$1=$B$2:$B$25)*ROW($A$2:$A$25)),FALSE)

Faseeh

 

[guatelize]

Thanks, works perfectly.

regards