Forum Discussion

Robobobob's avatar
Robobobob
Copper Contributor
Jul 19, 2023

Matching cell values on 2 criteria

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 ea...
excel
Formulas and Functions
sookoon's avatar
sookoon
Copper Contributor
Jul 20, 2023

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),"")

 

Sheet1

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

 

Sheet2

 Question 1Question 2Question 3Question 4
Person 123  

 

 https://support.microsoft.com/en-au/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759

Resources