Matching cell values on 2 criteria

Copper Contributor

I have two separate sets of data that I'm trying to combine in to one. Sheet 1 has one row per person, and multiple columns for each of their responses to a question. Sheet 2 has multiple rows for each person's responses (i.e. 4 separate rows for each of Person 1's responses to 4 different questions). Screenshots below for an example.


What I'm trying to do is find a formula to look up Person 1's response to Question 1 on Sheet 2, and fill in that answer in the respective row and column on Sheet 1 (and so on and so forth for each question and each person). I tried using INDEX and MATCH with the formula below, but get a SPILL of too many results, when my expectation is it only should return one result, so obviously I have something wrong, or it won't work in this situation. 




Any advice on a formula or how to address this is much appreciated!


Sheet 1:



Sheet 2:





2 Replies



You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

Hi @Robobobob 


I think the more natural formula is to use FILTER. You Microsoft 365 or newer versions to have FILTER. My formula is something like below


=FILTER(Sheet2!C2:C9, (Sheet2!A2:A9=E2) * (Sheet2!B2:B9=F1),"")



Person 1Question 12
Person 1Question 23
Person 1Question 34
Person 1Question 45
Person 2Question 19
Person 2Question 21
Person 2Question 35
Person 2Question 46



 Question 1Question 2Question 3Question 4
Person 123