INDEX MATCH formula help - mulitple queries with multiple responses

Hi,

I'm pretty good with Google Sheets and Excel but I've been trying to get a formula to work for the following situation for a few days now and just can't get it to work - any help would be great.

I have a google form which staff use to enter the following: Name, Branch, Day of the week, Profit and Week number

Once the data has been entered another worksheet in the same spreadsheet collates the information and gives me the total profit for each person each week. However I can only get the formula to display one person (the first person from the responses that matches the Branch and Week) so anyone else working in the same branch that week wont show.

this is the formula I'm using at the moment

=INDEX('Form responses 1'!$A$2:$C$3,MATCH($L$3&B7,'Form responses 1'!$C$2:$C$3&'Form responses 1'!$F$2:$F$3,0),2)

any help would be great

Thanks

Martin

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


Remember to desensitize the data.