Basic lookup with multiple conditions

[RichardW]

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 · Accepted solution]

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 wrote:

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

[RichardW]

Thanks Aurelien!