Match a static value with a list of values using MATCH()

[punitpopli]

Hi All,

I want to match/search a static value with the list of values and based on the match it should return me 1 or 0

expression am using - =IF(wildMatch('sheet3',Concat(distinct source,','))>0,1,0)

source column has sheet1, sheet2, sheet3 values

may I know if their is any way to handle this scenario

[rubenmarin]

Hi, the function will look the list of values, not expressions, you can convert the expression to values using $-expansion, like:

=IF(wildMatch('sheet3',$(=Chr(39) & Concat(distinct source,Chr(39) & ',' & Chr(39)) & Chr(39)))>0,1,0)

"$(=Chr(39) & Concat(distinct source,Chr(39) & ',' & Chr(39)) & Chr(39))" will be converted to 'sheet1',' sheet2','sheet3', so the expression to evaluate will be:

=IF(wildMatch('sheet3','sheet1',' sheet2','sheet3')>0,1,0)