Filtered Dropdown from Ref dropdown

[Boa_Casa]

Hi,

I have a column Item Name which contains ref to another table with all product details and I want to have a if condition for this i.e. If curtain track is bent then in Item name drop down only product with name “Forest” should come else rest of all the products can come.

I have written a expression as below but getting the below error.

Expression : If([Is the Curtain Track bent] = yes,LOOKUP([Item Name]=Forest,Curtain Track,Item Name,Item Name),LOOKUP([Item Name],Curtain Track,Item Name,Item Name))

Error : The expression is valid but its result type ‘Ref’ is not one of the expected types: Yes/No, List

Any idea on how can I resolve this issue?

[MultiTech]

The problem you’re running into there is the fact that the valid if is expecting you to provide a list of values - LOOKUP() is a single.

• That’s why it’s complaining about a “Ref”

Instead of using LOOKUP(), which is what you use when you want to find a SINGLE value;

• you need to use SELECT(), which is what you use when you want to generate a LIST of values.

---

Another version of your formula would be:

If([Is the Curtain Track bent] = "yes",
  SELECT(Products[ProductID], [Item Name] = "Forest"), 
Products[ProductID]
)

[Boa_Casa]

Thanks for the solution