Check for Presence of a Value in a Column Based on a List

[dwgnome]

In a separate column I would like to answer "Yes" or "No" if there is a value greater than zero somewhere in the same row if that number falls under the column header that matches a predefined list. It can be more than one number meeting this criteria, but it has to be at least one in the row.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unit No
[/TD]
[TD]SOP
[/TD]
[TD]apple
[/TD]
[TD]pear
[/TD]
[TD]oranges
[/TD]
[TD]nuts
[/TD]
[TD]banana
[/TD]
[/TR]
[TR]
[TD="align: center"]45
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]7
[/TD]
[/TR]
[TR]
[TD="align: center"]88
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]No
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Predefined List

Nuts
Pear
Banana

Also is it possible to have the formula allow for expanding predefined list.

Thank you.

 

[Aladin Akyurek]

In a separate column I would like to answer "Yes" or "No" if there is a value greater than zero somewhere in the same row if that number falls under the column header that matches a predefined list. It can be more than one number meeting this criteria, but it has to be at least one in the row.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unit No
[/TD]
[TD]SOP
[/TD]
[TD]apple
[/TD]
[TD]pear
[/TD]
[TD]oranges
[/TD]
[TD]nuts
[/TD]
[TD]banana
[/TD]
[/TR]
[TR]
[TD="align: center"]45
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]7
[/TD]
[/TR]
[TR]
[TD="align: center"]88
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]No
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


Predefined List

Nuts
Pear
Banana

Also is it possible to have the formula allow for expanding predefined list.

Thank you.

The banana entry in the first row has a trailing space. Such needs to be avoided.

A1:G5 houses the relevant data...

B2, control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,
  MATCH(IF($C2:$G2,$C$1:$G$1),List,0))),"Yes","No")

where List refers to a range housing the predefined list. This list can be extended at will. If so desired, it can be defined as a dynamic named range. Let A2:A4 on a sheet called Admin house the current list and define List as referring to:

=Admin!$A$2:INDEX(Admin!$A:$A,MATCH(REPT("z",255),Admin!$A:$A))

 

[dwgnome]

Thank you Aladin for your quick solution, it worked perfectly!

 

[Aladin Akyurek]

Thank you Aladin for your quick solution, it worked perfectly!

You are welcome. Thanks for providing feedback.