List of Values based on value existing in a row


I want to try solve the following query with an expression if possible.

I have a table called Checklists and columns [Check 1], [Check 2], [Check 3], [Check 4], [Check 5] and a 6th column of type Enum called [Failed Check List].

If the values in the first 5 columns are Pass, Pass, Fail, Pass, Fail is there a way that the list of column names that contain “Fail” be available in the [Failed Check List] column dropdown without having to write and OR(CONTAINS()) expression for each column. Is there a way that the expression could be written to search the ROW for the “Fail” value instead?

I used the Tip here to create a small table called All Headers that contains all the headers of my Checklists Table but not sure if this can help with this instance.

Hope this makes sense. Any advice or better way of achieving this would be great.

Thank you.

Nope. :frowning:

    IFS(("Fail" = [Check 1]), "Check 1"),
    IFS(("Fail" = [Check 2]), "Check 2"),
    IFS(("Fail" = [Check N]), "Check N")
  - LIST("")

Many thanks @Steve

1 Like

Is this still working for you ?

Hi @Rifadm817. Yes I am still using the solution suggested by Steve and it is working as required.