I have 3 worksheets, Sales, Members and Payments. I am doing my sales and then for the transaction select Member A with a sales price say 100 and 300 (Member is cell B4 and Price is Cell D4 on SALES worksheet). the transactions is calculated to MEMBER sheet D3 with a sumifs, index and match formula
Formaula =(SUMIFS(Sales!$D$4:$D$9,Sales!$B$4:$B$9,Members!B3).
Sales & Members
Members.
Now on the payments sheet i want to enter multiple payments for membera and member b, which then needs to be deducted from !MEMBERS D3 $ D4 but the problem is is that vlookup and index(match only select one value and not multiple values, so the update value is wrong.Payments.
=(SUMIFS(Sales!$D$4:$D$9,Sales!$B$4:$B$9,Members!B3)- INDEX(Payments!$B$3:$B$5,MATCH(Members!B3,Payments!$A$3:$A$9,1))))

What I am trying to achieve is that D4 on !MEMBERS D4 should be updated according to the lookup value of memberA from Sales MINUS payments for MEMBER A (multiple payments).