Compare value in 1 column to values in multiple columns

[Garmerr]

Hello - I would like to compare the value in one column to see if that value is included in a list of values across multiple columns. The purpose of this to see if the actual value is present in the list of possible values. If it is included, then there should be a confirmation statement (e.g., Included). If it is not included, there should be some guidance statement (e.g. Select new Value).

I have thought about trying to concat the possible values and searching the resulting string, but not sure if there is an easier approach.

Data Setup

Actual Value	Name
2001	CP10
1995	CP11
4300	CP08

Name	Possible Value1	Possible Value 2	Possible Value 3	Possible Value 4	Possible Value X
CP10	2001	2002	2300
CP11	1997	2001
CP08	1000	1500	2000	4300	9999

Ideal Result

Name	Result
CP10	Included
CP11	Select new Value
CP08	Included

 

[mumps]

Formula in C2 and copied down:

=IF(COUNTIF(F2:J2,A2)>0,"Included","Select New Value")

Book1
	A	B	C	D	E	F	G	H	I	J
1	Actual Value	Name	Result		Name	Possible Value1	Possible Value 2	Possible Value 3	Possible Value 4	Possible Value X
2	2001	CP10	Included		CP10	2001	2002	2300
3	1995	CP11	Select New Value		CP11	1997	2001
4	4300	CP08	Included		CP08	1000	1500	2000	4300	9999
Sheet1

 

[KRice]

If your Actual Value and Name are on the same row as the possible values, then @mumps solution is ideal. However, if the Actual Value and Name aren't in the same row as the possible values, or perhaps they appear in a different order than the lookup table, then you'll need to: 1) confirm that you have a Name match, and 2) determine which row in the lookup table is needed. Then a more complicated solution is needed...here is one approach:

Book1
	A	B	C	D	E	F
1	Name	PossVal1	PossVal2	PossVal3	PossVal4	PossVal5
2	CP10	2001	2002	2300
3	CP11	1997	2001
4	CP08	1000	1500	2000	4300	9999
Sheet1

Book1
	A	B	C
7	Actual Value	Name	Result
8	2001	CP10	included
9	1995	CP11	select new value
10	4300	CP08	included
11	4300	CP09	no name match
12	999	CP08	select new value
Sheet1

Cell Formulas
Range	Formula
C8:C12	C8	=LET(a,MATCH(B8,$A$2:$A$4,0),b,SUM(--(INDEX($B$2:$F$4,a,)=A8)),IFS(ISERROR(a),"no name match",b>=1,"included",TRUE,"select new value"))

 

[Garmerr]

Formula in C2 and copied down:

=IF(COUNTIF(F2:J2,A2)>0,"Included","Select New Value")

Book1
	A	B	C	D	E	F	G	H	I	J
1	Actual Value	Name	Result		Name	Possible Value1	Possible Value 2	Possible Value 3	Possible Value 4	Possible Value X
2	2001	CP10	Included		CP10	2001	2002	2300
3	1995	CP11	Select New Value		CP11	1997	2001
4	4300	CP08	Included		CP08	1000	1500	2000	4300	9999
Sheet1

This method works.

 

[Garmerr]

If your Actual Value and Name are on the same row as the possible values, then @mumps solution is ideal. However, if the Actual Value and Name aren't in the same row as the possible values, or perhaps they appear in a different order than the lookup table, then you'll need to: 1) confirm that you have a Name match, and 2) determine which row in the lookup table is needed. Then a more complicated solution is needed...here is one approach:

Book1
	A	B	C	D	E	F
1	Name	PossVal1	PossVal2	PossVal3	PossVal4	PossVal5
2	CP10	2001	2002	2300
3	CP11	1997	2001
4	CP08	1000	1500	2000	4300	9999
Sheet1

Book1
	A	B	C
7	Actual Value	Name	Result
8	2001	CP10	included
9	1995	CP11	select new value
10	4300	CP08	included
11	4300	CP09	no name match
12	999	CP08	select new value
Sheet1

Cell Formulas
Range	Formula
C8:C12	C8	=LET(a,MATCH(B8,$A$2:$A$4,0),b,SUM(--(INDEX($B$2:$F$4,a,)=A8)),IFS(ISERROR(a),"no name match",b>=1,"included",TRUE,"select new value"))

This method works and is preferred for the multi table data set.