Basic lookup with multiple conditions

[anonymous]

I would be grateful for help with the best expression to use to perform a lookup between two tables using multiple criteria.

Example table

I want to populate the Contract ID from Table 2 into the Contract ID column of Table 1 when two conditions met:

1. Company name from Table 1 matches company name from Table 2

2. Region from Table 1 matches region name from Table 2

Thanks!

[Aurelien]

Hi @RichardW

I would suggest using a SELECT() expression: this will return a list of matching results.

Then wrap the result in a ANY() expression: this will pick any random item from the previous expression.

@RichardW

I want to populate the Contract ID from Table 2 into the Contract ID column of Table 1 when two conditions met:

1. Company name from Table 1 matches company name from Table 2

2. Region from Table 1 matches region name from Table 2

ANY(
  SELECT(Table1[ContractID],
    AND(
      [Company]=[_THISROW].[Company],
      [Region]=[_THISROW].[Region]
    )
  )
)

For reference:

SELECT() - AppSheet Help

ANY() - AppSheet Help

[anonymous]

Thanks Aurelien!