Excel Formula to find Matched value within multiple column

Question

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?

Why has the last row "No match"? It's the pendant to the first row ... — Ike
– Ike, Commented Oct 3, 2022 at 13:38
What if the match is on the same row for a given value of Tel col, but also in other column on a different row? How would be that situation handled? For example the following 2x3 array {1,0,1};{0,2,1}. On the first row 1 has a match on the same row (no Match), but on the second row there a match to 1 a valid one. So the result would be uncertain. It is not clear this statement to me for all possible scenarios: "if the match is in the same row, then it's still considered unmatched" — David Leal
– David Leal, Commented Oct 3, 2022 at 18:28

JvdV · Accepted Answer · 2022-10-03 15:24:31Z

3

Alternatively try MMULT():

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

edited Oct 3, 2022 at 15:24

answered Oct 3, 2022 at 14:21

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ike · Accepted Answer · 2022-10-03 14:11:43Z

2

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.

answered Oct 3, 2022 at 14:11

Ike

13.4k6 gold badges20 silver badges38 bronze badges

Comments

David Leal · Accepted Answer · 2022-10-03 20:40:21Z

2

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

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.

edited Oct 3, 2022 at 20:40

answered Oct 3, 2022 at 16:41

David Leal

6,8085 gold badges36 silver badges68 bronze badges

3 Comments

Ike Over a year ago

First I thought this is cool - but it is not the correct solution: if you put e.g. 675673 in B4, row 3 should give a match ...

David Leal Over a year ago

Thanks, @Ike, you were correct. I updated my answer. Now it works for the failed case. For the array version, I ended up in a variant of your solution. I gave you credit. To build the recurrence per row for calculating the count, I had to emulate it via SUM of 1s.

Ike Over a year ago

As always: there are several solutions :-)

Mayukh Bhattacharya · Accepted Answer · 2022-10-03 20:59:21Z

2

You may try this as well.

• Formula used in cell D2

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

edited Oct 3, 2022 at 20:59

answered Oct 3, 2022 at 14:24

Mayukh Bhattacharya

29.2k9 gold badges31 silver badges46 bronze badges

Comments

Sachin Kohli · Accepted Answer · 2022-10-03 13:55:22Z

0

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

answered Oct 3, 2022 at 13:55

Sachin Kohli

1,9961 gold badge4 silver badges6 bronze badges

Collectives™ on Stack Overflow

Excel Formula to find Matched value within multiple column

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related