Compare two columns to find similar value.

[pooja29]

Hello All,

I want a formula wherein column B value needs to be compared with C column values and if same values are there in these two columns it should show in D column. Can anyone please help me!

 

[jasonb75]

Try

=IF(B3="","",,IF(COUNTIF(C:C,B3)>=COUNTIF(B:3:B4,B3),"Match","No Match"))

 

[pooja29]

Try

=IF(B3="","",,IF(COUNTIF(C:C,B3)>=COUNTIF(B:3:B4,B3),"Match","No Match"))

It didn't work

 

[Fluff]

There were a couple of typos in Jason's formula, try

=IF(B3="","",IF(COUNTIF(C:C,B3)>=COUNTIF(B$3:B3,B3),"Match","No Match"))

 

[pooja29]

There were a couple of typos in Jason's formula, try

=IF(B3="","",IF(COUNTIF(C:C,B3)>=COUNTIF(B$3:B3,B3),"Match","No Match"))

Is it possible that the figure will show in column D instead of match. Means if Column B and C has same value that same figure should reflect in column D

 

[Rahul1987]

Is it possible that the figure will show in column D instead of match. Means if Column B and C has same value that same figure should reflect in column D

What Abt? Pooja29

Book1
	A	B	C	D	E
1	Colum A		Colum C		Colum E
2	List1				Output
3	500				500
4	100				No Match
5	1500				1500
6			List2
7			500
8			147
9			1500
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=IFERROR(VLOOKUP($A3,C:C,1,0),"No Match")

 

[jasonb75]

Is it possible that the figure will show in column D instead of match.

This is basic formula editing, you change what you don't want to what you do want.

=IF(B3="","",IF(COUNTIF(C:C,B3)>=COUNTIF(B$3:B3,B3),B3,"No Match"))

What Abt?

Your method doesn't allow for unique matching of duplicate values.

 

[Rahul1987]

Your method doesn't allow for unique matching of duplicate values.

@Jason75..I would suggest , let her revert.

 

[jasonb75]

I try to think out of the box with my suggestions.

When people ask for help with things that we would look at as beginner questions, we have to do a bit of additional thinking for them and identify potential problems that they may not be able to.

Book3
	B	C	D	E
2	List1		Output	Output
3	500		500	500
4	100		No Match	No Match
5	1500		1500	1500
6	500	List2	No Match	500
7		500		No Match
8		147		No Match
9		1500		No Match
Sheet1

Cell Formulas
Range	Formula
D3:D9	D3	=IF(B3="","",IF(COUNTIF(C:C,B3)>=COUNTIF(B$3:B3,B3),B3,"No Match"))
E3:E9	E3	=IFERROR(VLOOKUP($B3,C:C,1,0),"No Match")