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
Look up in another table based on multiple fields
+2
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)
word1Matching Field 2 (Single Line Text)
word2Matching Field 3 (Single Line Text)
word3Combined (formula)
word1,word2,word3Table 2 Records (Single Line Text)
Table 2
Combined (formula, left-most column)
word1,word2,word3Matching Field 1 (Single Line Text)
word1Matching Field 2 (Single Line Text)
word2Matching 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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.