LOOKUP if one of the columns filled in by the user matches

Good afternoon, first I thank you in advance for your help.

Please could you give me an expression to be able to obtain the value of a column in a table if any data from 4 columns of another table matches.

The user must complete this information in a form and if any data from this list matches

Identification document,
Email,
Telephone,
Mobile phone

The expression must search in another table under the name Pre-Approval Data and bring the result from the Agent Code column.

please could you help me with this expression since I have tried but I cannot understand very well this expression of LOOKUP for the search of various data

Try:

SELECT(
  Pre-Approval[Agent Code],
  OR(
    AND(
      ISNOTBLANK([_THISROW].[Identification document]),
      ([_THISROW].[Identification document] = [Identification document])
    ),
    AND(
      ISNOTBLANK([_THISROW].[Email]),
      ([_THISROW].[Email] = [Email])
    ),
    AND(
      ISNOTBLANK([_THISROW].[Telephone]),
      ([_THISROW].[Telephone] = [Telephone])
    ),
    AND(
      ISNOTBLANK([_THISROW].[Mobile phone]),
      ([_THISROW].[Mobile phone] = [Mobile phone])
    ),
  )
)
3 Likes

More efficient:

UNIQUE(
  IFS(
    ISNOTBLANK([Identification document]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Identification document] = [Identification document])
      )
  )
  + IFS(
    ISNOTBLANK([Email]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Email] = [Email])
      )
  )
  + IFS(
    ISNOTBLANK([Telephone]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Telephone] = [Telephone])
      )
  )
  + IFS(
    ISNOTBLANK([Mobile phone]),
      SELECT(
        Pre-Approval[Agent Code],
        ([_THISROW].[Mobile phone] = [Mobile phone])
      )
  )
)
2 Likes

Sorry Steve, I can’t see the LOOKUP function

Wrap either of the expressions I provided with ANY() to get the same result as LOOKUP().

Sorry Steve I think I have not explained myself well, when a user enters the data in the form in the columns

DNI | NIE | CIF
Correo Electrónico
**Teléfono **
Teléfono Móvil

I must look in the table “Pre-Approval Data” these data of the columns entered by the user and if one of them matches I must bring the value of the column "Agent Code"

This is why I mentioned that I understand that this should work with the expression LOOKUP ()

First, this:
image

means that this table name:
image

cannot have a - character in its name. You will need to rename the table to remove the - character.

Second, wrap the entire expression with ANY():

ANY(
UNIQUE(
...
)
)

The result should be what you want.

LOOKUP() has limitations and is intended for situations simpler than yours; it is not a good choice for your needs as I understand them.

Sorry Steve, in this table I have many expressions if I change the name of the table this means that I have to delete the current table and reload it because it would lose all the detailed expressions in the columns, and this table has more than 100 columns.

Did you open any way to change this without losing expressions?

:sob:

Not at all! Just rename it here:

You will, however, have to change its name wherever it appears in expressions.