Multiple value lookup

[Colin_Lough]

Hi,

I have an expression where based on the logged in user’s team name, a slice will return a subset of data from my main table. I went with an assumption that 1 user has 1 team, but now have a use case where a user wants to see multiple team allocations. Can anyone suggest an edit to the formula below, please?

I’m looking to return the rows where the user could have 2 x “Team Name” listed in the Users table, i.e., Team A , Team B

AND([Portfolio Status]= “Active” ,
[team name] = LOOKUP( USEREMAIL() , “Users” , “Useremail” , “Team Name” ))

[Suvrutt_Gurjar]

Colin_Lough:

AND([Portfolio Status]= “Active” ,
[team name] = LOOKUP( USEREMAIL() , “Users” , “Useremail” , “Team Name” ))

Maybe

AND([Portfolio Status]= “Active” ,
IN([Team Name] = SELECT(Users [Team Name] , [Useremail] =USEREMAIL())))

[Colin_Lough]

Thanks, @Suvrutt_Gurjar

Error:
Cannot compare Ref with List in ([Team Name] = SELECT(Users[Team Name],([Useremail] = USEREMAIL())))

[Suvrutt_Gurjar]

Could you mention, in which table is the [Team Name] as key column, Users or where the slice is based on?
And type of [Team Name] columns in bot the tables

[Colin_Lough]

The slice is based upon table ‘New Legal Obligations’
Within this table [Team Name] is Enum of type Ref to lookup table ‘Teams’

Within Table ‘Teams’, column [Team Name] is of column type ‘Name’

And [Team Name] is key within table Teams

[Suvrutt_Gurjar]

Could you please share the final expression screenshot and the error as in the first post.

Any specific reason, you added a parenthesis before [Useremail] in the above expression?

Colin_Lough:

([Team Name] = SELECT(Users[Team Name],([Useremail] = USEREMAIL())))

[Colin_Lough]

Suvrutt_Gurjar:

AND([Portfolio Status]= “Active” ,
IN([Team Name] = SELECT(Users [Team Name] , [Useremail] =USEREMAIL())))

No reason I added the extra parenthesis, maybe just poor coding.

[Colin_Lough]

Suvrutt_Gurjar:

AND([Portfolio Status]= “Active” ,
IN([Team Name] = SELECT(Users [Team Name] , [Useremail] =USEREMAIL())))

Actually that extra parenthesis is just showing in the error, not in the actual expression

[Suvrutt_Gurjar · Accepted solution]

Oh , I am sorry, my bad of an in advertently inserted incorrect operator in the expression

Please try
AND([Portfolio Status]= “Active” ,
IN([Team Name], SELECT(Users [Team Name] , [Useremail] =USEREMAIL())))

[Colin_Lough]

Suvrutt_Gurjar:

AND([Portfolio Status]= “Active” ,
IN([Team Name], SELECT(Users [Team Name] , [Useremail] =USEREMAIL())))

Spot on! Thanks so much, @Suvrutt_Gurjar

[Suvrutt_Gurjar]

You are welcome @Colin_Lough

I am sorry that my one inadvertent and unintended placement of an equality opertor caused the post thread to get extended. Otherwise, the post thread need not have extended beyond post 2. I am sorry , however also happy now that it finally works per requirement.