Index match

[Traceelewis71]

I dont know what ive done wrong to my index match statement. I need to get monthly earning into a column. each person has 12 months in a row. On a separate spreadsheet I have the dollar amounts per month for each person. I need an index match statement that will put the 9.50% in the column. please help
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]9.50%[/TD]
[TD]Person ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]?[/TD]
[TD]A02257[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]A02257[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]A02257[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]A02257[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 304"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]person ID[/TD]
[TD]9.5% of Gross[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]A02257[/TD]
[TD="align: right"]89.63[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]A02257[/TD]
[TD="align: right"]84.59[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]A02257[/TD]
[TD="align: right"]85.19[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]A02257[/TD]
[TD="align: right"]94.59[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]A02257[/TD]
[TD="align: right"]82.33[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]A02257[/TD]
[TD="align: right"]95.16[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]A02257[/TD]
[TD="align: right"]89.98[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]A02257[/TD]
[TD="align: right"]86.88[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]A02257[/TD]
[TD="align: right"]95.16[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]A02257[/TD]
[TD="align: right"]82.75[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]A02257[/TD]
[TD="align: right"]82.75[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]A02257[/TD]
[TD="align: right"]91.02[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Assuming the data are in Sheet2 columns A:C, headers in row 1, maybe this array formula in Sheet1 B2 copied down

=IF(C2="","",INDEX(Sheet2!C$2:C$100,MATCH(1,IF(Sheet2!A$2:A$100=A2,IF(Sheet2!B$2:B$100=C2,1)),0)))
confirmed with Ctrl+Shift+Enter, not just Enter

Or

Regular formula in B2 copied down
=IF(C2="","",SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,C2))

M.