Look up in another table based on multiple fields

Imagine I have 2 tables that have 3 columns with identical fields. I want to be able to look up the name of the record in the other table if the 3 column values match. Any ideas? I can’t use lookup because the whole point is to avoid a manual lookup. If I use a Formula field, I don’t know how to refer to another table. In Google Sheets I would have used a Filter() formula

Filter() unfortunately doesn’t exist on this platform. I’d like it to (among other functions).

Here’s a workaround that could work on an adhoc basis and still get you results in batch.

Setup a base like this:

Table 1
Matching Field 1 (Single Line Text)
word1

Matching Field 2 (Single Line Text)
word2

Matching Field 3 (Single Line Text)
word3

Combined (formula)
word1,word2,word3

Table 2 Records (Single Line Text)

Table 2

Combined (formula, left-most column)
word1,word2,word3

Matching Field 1 (Single Line Text)
word1

Matching Field 2 (Single Line Text)
word2

Matching Field 3 (Single Line Text)
word3

When you’d like to do your lookup, copy paste all the fields from Table 1 > Combined into Table 1 > Table 2 Records. Then, change the field type of Table 1 > Table 2 Records to a ‘Linked Record’ and link it to Table 2. All of your fields will now be linked to the matching fields in the other table. But you’ll have to reset Table 2 Records to single line text and then back to linked record if any new fields need to be linked up.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.