CODEWITHSUNDEEP
excelexcel-formula
izzatfi
izzatfi

Reputation: 25

Excel Formula to find Matched value within multiple column

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?

Upvotes: 0

Views: 426

Answers (5)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

You may try this as well.

enter image description here

• Formula used in cell D2

=IF(SUM(N(A$2:C$7=A2))>SUM(N(A2:C2=A2)),"Match","No Match")

Upvotes: 2

David Leal
David Leal

Reputation: 6759

Lets give a try to an array version in F2 (this is an extension of @Ike's answer)

=LET(cntRange, COUNTIF(A2:C7,A2:A7), 
  cntRow,BYROW(--(A2:C7=A2:A7), LAMBDA(row, SUM(row))),
  IF((cntRange - cntRow) > 0, "Match", "No Match"))

or without LET:

=IF((COUNTIF(A2:C7,A2:A7) - 
  BYROW(--(A2:C7=A2:A7),LAMBDA(row, SUM(row)))) > 0, "Match", "Not Match")

sample excel file

The trick here is to emulate COUNTIF with --(A2:C7=A2:A7) it returns a 6x3 matrix and per row as many 1s as repetition we have of each cell of A2:A7, then SUM(row) does the magic of the count.

Upvotes: 2

JvdV
JvdV

Reputation: 75960

Alternatively try MMULT():

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

Upvotes: 3

Ike
Ike

Reputation: 13064

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.

Upvotes: 2

Sachin Kohli
Sachin Kohli

Reputation: 1986

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

Upvotes: 0

Related Questions