Group Different Columns

[Khamadohn]

Hi,
i have two tables, Form and CRM. In CRM i have [VAT],[Email 1],[Email 2],[Email 3],[Email 4],[Email 5] and in Form [VAT],[Email].
How can i show (in an enum?) in Form[Email] the values of CRM[Email 1],[Email 2],[Email 3],[Email 4],[Email 5] from the rows where the [VAT] from the two tables is the same?

[Joseph_Seddik]

What are the key columns in your tables? is it VAT?

[Steve · Accepted solution]

As the Suggested values expression for the Email column of the Form table:

IFS(
  ISNOTBLANK([VAT]),
    SORT(
      SELECT(CRM[Email 1], ([_THISROW].[VAT] = [VAT]))
      + SELECT(CRM[Email 2], ([_THISROW].[VAT] = [VAT]))
      + SELECT(CRM[Email 3], ([_THISROW].[VAT] = [VAT]))
      + SELECT(CRM[Email 4], ([_THISROW].[VAT] = [VAT]))
      + SELECT(CRM[Email 5], ([_THISROW].[VAT] = [VAT]))
      - LIST("")
    )
)

[Khamadohn]

Thank you! I've been on this for a week!