Compare if a value in Range exists in a separate multi-column Range

[CMSREPORTS]

I am working on a simple project to see if any items of a range exists in a second multi column range
For some reason, i always get a FALSE answer when matching an item in the Shopping List 2.
Shopping List 1 and 3 work fine
I also tried to do a 4th list, but that didn't work at all.

I know I am missing something obvious.

	A	B	C	D	E	F	G	H	I	J	K
1	Item1	Item2	Item3		At least one item on the Shopping List:				Shopping List 1	Shopping List 2	Shopping List 3
2	Apple	Pear	Orange		TRUE​	=SUMPRODUCT(--(A2:C2=SL_13))>0			Apple	Food	food
3	Banana	Strawberry	Peach		FALSE​	=SUMPRODUCT(--(A3:C3=SL_13))>0			Food	Food	food
4	Bread	Cheese	Cola		TRUE​	=SUMPRODUCT(--(A4:C4=SL_13))>0			Food	Peach	food
5									Food	Food	food
6									Food	Food	Cola
						Named Range SL_13: i1:k6

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


That formula is comparing A2 with col I, B2 with col J & C2 with col K.
Is that what you want?

 

[CMSREPORTS]

I want to compare each A2, B2, and C2 to I$2:K$6, then and return TRUE if >0 items match
and do that for each row.

I just tried this.... =SUMPRODUCT(COUNTIF(B2:D2,$I$2:$K$6))>0

This seems to have done the trick. Simple but it works.

 

[Fluff]

Glad you sorted it & thanks for the feedback.