Help with formula

[LaurenJez]

Hello. I need a formula that looks for the value in a cell and compares it to a range of cells to throwback if there is a match anywhere in the range of cells. I would also like the formula to exclude considering blank cells so I do not end up with false No Matches. Example of the data is below.

Layman's Terms:
I want to know if A2 (apple) appears anywhere in D2:F2. In this example, I would expect Match.
I want to know if B4 (banana) appears anywhere in D4:F4. In this example, I would expect Match.
I want to know if the value in C5 (melon) appears anywhere in D5:F5. In this example, I would expect No Match

Column A	Column B	Column C	Column D	Column E	Columnn F
Apple			Apple
Orange				Orange
Apple	Banana		Apple		Banana
Orange	Kiwi	Melon	Orange	Kiwi

 

[etaf]

not sure i understand why the changes in the cell reference and the range ?

countif()>0 would work within an IF

I would also like the formula to exclude considering blank cells so I do not end up with false No Matches.

but i'm not sure i understand fully your question

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	Apple			Apple			Match
3	Orange				Orange		Match
4	Apple	Banana		Apple		Banana	Match
5	Orange	Kiwi	Melon	Orange	Kiwi		No Match
Main

Cell Formulas
Range	Formula
G2:G5	G2	=IF(COUNTIFS(D2:F2,TAKE(FILTER(A2:C2,A2:C2<>""),,-1)),"Match","No Match")

 

[LaurenJez]

not sure i understand why the changes in the cell reference and the range ?

countif()>0 would work within an IF


but i'm not sure i understand fully your question

I want to know if the value in A2 shows up anywhere in D2 to F2
I want to know if the value in B2 shows up anywhere in D2 to F2
I want to know if the value in C2 shows up anywhere in D2 to F2

Since in the first row, B2 and C2 is blank, I don't want there to be a "Match" since there are also blanks in D2 to F2

 

[Fluff]

Do all 3 have to match, or just one of them?

 

[kvsrinivasamurthy]

Show the expected results like data.

 

[LaurenJez]

Hello. I need a formula that looks for the value in a cell and compares it to a range of cells to throwback if there is a match anywhere in the range of cells. I would also like the formula to exclude considering blank cells so I do not end up with false No Matches. Example of the data is below.

Layman's Terms:
I want to know if A2 (apple) appears anywhere in D2:F2. In this example, I would expect Match.
I want to know if B4 (banana) appears anywhere in D4:F4. In this example, I would expect Match.
I want to know if the value in C5 (melon) appears anywhere in D5:F5. In this example, I would expect No Match

Column A	Column B	Column C	Column D	Column E	Columnn F
Apple			Apple
Orange				Orange
Apple	Banana		Apple		Banana
Orange	Kiwi	Melon	Orange	Kiwi

Column A	Column B	Column C	Column D	Column E	Columnn F	Ideal formula would return Column A vs. Colums D to F	Ideal formula would return Column B vs. Colums D to F	Ideal formula would return Column C vs. Colums D to F
Apple			Apple			Match
Orange				Orange		Match
Apple	Banana		Apple		Banana	Match	Match
Orange	Kiwi	Melon	Orange	Kiwi		Match	Match	No Match

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Apple			Apple			Match
3	Orange				Orange		Match
4	Apple	Banana		Apple		Banana	Match	Match
5	Orange	Kiwi	Melon	Orange	Kiwi		Match	Match	No Match
Main

Cell Formulas
Range	Formula
G2:I5	G2	=IF(A2="","",IF(COUNTIFS($D2:$F2,A2),"Match","No Match"))

 

[LaurenJez]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Apple			Apple			Match
3	Orange				Orange		Match
4	Apple	Banana		Apple		Banana	Match	Match
5	Orange	Kiwi	Melon	Orange	Kiwi		Match	Match	No Match
Main

Cell Formulas
Range	Formula
G2:I5	G2	=IF(A2="","",IF(COUNTIFS($D2:$F2,A2),"Match","No Match"))

THAT IS IT! Worked like a charm. Thank you SO much, I really really appreciate it! Side question, what does putting "$" do to the formula?

 

[Fluff]

It locks the columns so they don't change when you drag the formula across.