Comparing multiple columns

[KirkExcel]

Have been trying to get this to work for some time, but cannot.

I need to compare two sets of columns and return a value ie "MATCH" if a match is found.

The four columns in question are two lots of "ID" and Account Number".

The match can only be returned if all respective columns match (ie, "ID" in sheet one matches "ID" in the sheet two with the corresponding "Account Number" cells also matching. Kind of like an array search based on the initial criteria.

Have tried MATCH, INDEX and a few others but something is not right.

Any ideas?

Many thanks.

[#2]

@KirkExcel 

=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.

 

[#3]

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).

[#4 · Accepted solution]

@KirkExcel 

=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.

[#5]

Thank you very much, it now works.