Excel Formula to find Matched value within multiple column-CodePudding

I have a dataset such as below in Excel:

Tel	Mob	Off	Checking
45345	9473	5356	Match
675673	35232	786547	No match
54657	1353	42545	No match
534734	534734	546	No match
24566	5456	4525	No match
45345	1343	26436	Match

Then, I would like to check whether the Tel column has matched with any of the Tel, Mob and Off column. However, if the match is in the same row, then it's still considered unmatched. The Checking column is the exactly output that I want.

I have try using this simple formula, however, it's still didn't get the output as I wanted.

IF((COUNTIF($A$2:$A$7,A2)   COUNTIF($B$2:$B$7,A2)   COUNTIF($C$2:$C$7,A2))<2,"No match","Match")

Is there any other formula in Excel that could cater this other than using Kutools or VBA?

CodePudding user response：

This one is a bit shorter :-)

=IF(COUNTIF($A$2:$C$7,A2) - COUNTIF(A2:C2,A2) > 0,"Match","No match")

It first counts the all occurences of Tel in the whole matrix and then removes the ones from the same row (e.g. 534734). If the result is > 0 then there are matches.

CodePudding user response：

Not a short one, but works with one formula, no need to fill since it will spill ,

• Formula used in cell D2

=BYROW(A2:C7,LAMBDA(x,IF(ISERROR(SEARCH(A2,TEXTJOIN("|",,x))),"No Match","Match")))

CodePudding user response：

Alternatively try MMULT():

=IF(SUM(N(MMULT(N(A$2:C$7=A2),{1;1;1})>0))>1,"","No ")&"Match"

CodePudding user response：

Lets give a try to an array version in F2:

=BYROW(COUNTIF(A2:A7,A2:C7),LAMBDA(row,IF(MAX(row)>1, "Match", "No Match")))

Note: I tested that when I change A7 with 0 for example I get No Match in all rows.

Easy to understand: COUNTIF(A2:A7,A2:C7) returns:

 --- --- --- 
| 2 | 0 | 0 |
 --- --- --- 
| 1 | 0 | 0 |
 --- --- --- 
| 1 | 0 | 0 |
 --- --- --- 
| 1 | 1 | 0 |
 --- --- --- 
| 1 | 0 | 0 |
 --- --- --- 
| 2 | 0 | 0 |
 --- --- ---

ensuring per row that the maximum is greater than 1, is the Match condition

CodePudding user response：

I got this formula, Not sure if there is any direct way though;

=IF(COUNTIFS(A2:C2,A2)>1,"No Match",IF(OR(COUNTIFS($A$2:$A$7,A2)>1,COUNTIFS($B$2:$B$7,A2)>0,COUNTIFS($C$2:$C$7,A2)>0),"Match","No Match"))

Hope this Helps...