SOLVED

# Comparing multiple columns

Occasional Contributor

# Comparing multiple columns

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.

4 Replies

# Re: Comparing multiple columns

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

# Re: Comparing multiple columns

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

best response confirmed by KirkExcel (Occasional Contributor)
Solution

# Re: 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.

# Re: Comparing multiple columns

Thank you very much, it now works.