Help with slice expression

[Lucinda_Mason]

The problem seems to be with the IN([TEAM line. It does NOT find the matches it should.

[TEAM_NO] from the record is always a single value.
TEAM_NO from the USERS table can have one or more values

What am I missing?

AND(
[SCOPE_NO]=10,[START_DATE]<=Today(),[END_DATE]+7>=Today(),ISNOTBLANK([TEAM_NO]),[ANSWERED]=“Keep Praying”,
IN([TEAM_NO], LOOKUP(USEREMAIL(), Users, USER_EMAIL, TEAM_NO))
)

The list below SEEMS to be what I want.
ALL these statements are true:
…1: (The value of column ‘SCOPE_NO’) is equal to (10)
…2: (The value of column ‘START_DATE’) is less than or equal to (TODAY())
…3: ((The value of column ‘END_DATE’ + 7) is greater than or equal to (TODAY())
…4: This statement is false:
…(The value of column ‘TEAM_NO’) is empty
…5: (The value of column ‘ANSWERED’) is equal to (“Keep Praying”)
…6: (The value of column ‘TEAM_NO’) is one of the values in the list (One randomly chosen value from this list (
…The list of values of column ‘TEAM_NO’
…from rows of table ‘Users’
…where this condition is true: ((The value of column ‘USER_EMAIL’) is equal to (USEREMAIL()))))

[Steve · Accepted solution]

Try replacing the existing IN() subexpression with either of the following:

ISNOTBLANK(
  FILTER(
    "Users",
    AND(
      ([USER_EMAIL] = USEREMAIL()),
      IN([_THISROW].[TEAM_NO], [TEAM_NO])
    )
  )
)

or:

IN(
  [TEAM_NO],
  SPLIT(
    LOOKUP(
      USEREMAIL(),
      "Users",
      "USER_EMAIL",
      "TEAM_NO"
    ),
    " , "
  )
)

The problem is that LOOKUP() always produces a singular value. If the value it’s asked to provide is a list, it converts the list to a single Text value that then needs to be converted back into a list to be used as a list.

[Lucinda_Mason]

Steve:

ISNOTBLANK(
  FILTER(
    "Users",
    AND(
      ([USER_EMAIL] = USEREMAIL()),
      IN([_THISROW].[TEAM_NO], [TEAM_NO])
    )
  )
)

Steve,

THANKS!! It works like I need it to.

Appreciate the help and the speed.

[Steve]