Forum Discussion
Comparing multiple columns
=IFNA(IF(MATCH(1,(A2=sheet2!$F$2:$F$190)*(sheet1!B2=sheet2!$G$2:$G$190),0),"Match",""),"")I've tried the formula in the attached file and it returns a match if ID "XYZ" and Account Number "123" are in cells A2&B2 in sheet 1 and in cells F34&G34 in sheet2.
The formula still returns "Match" if ID "XYZ" and Account Number "123" are in cells F111&G111 in sheet 2 for example.
The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or Excel 2021.
=IFNA(IF(MATCH(1,(A2=$F$2:$F$18)*(B2=$G$2:$G$18),0),"Match",""),"")You can try this formula with the data layout in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Many thanks for your reply.
However, it doesn't seem to work. I need it to return a match regardless of which row the matching value(s) is/are in.
So for example ID "XYZ" and Account Number "123" in cells A2 & B2 would return a match if the ID and Account Number is the second sheet was located in, let's say F34 & G34 (or any other row).
=IFNA(IF(MATCH(1,(A2=sheet2!$F$2:$F$190)*(sheet1!B2=sheet2!$G$2:$G$190),0),"Match",""),"")I've tried the formula in the attached file and it returns a match if ID "XYZ" and Account Number "123" are in cells A2&B2 in sheet 1 and in cells F34&G34 in sheet2.
The formula still returns "Match" if ID "XYZ" and Account Number "123" are in cells F111&G111 in sheet 2 for example.
The formula has to be entered with ctrl+shift+enter if one doesn't work with Office365 or Excel 2021.
- Thank you very much, it now works.