Another VLookup question (no solutions working)

[Justjack296]

I have spent conservatively 3 hours reading posts and watching videos on linking records, lookup fields, etc. It seems that an enormous number of Airtable users are facing something similar - the need to automatically relate to tables but pulling data from one into the other. I have two tables, (Table 1) A table of submitted results/answers and (Table 2) A table that pairs each potential answer with a "response." So as a new record is created in Table 1 (through API with Typeform) with user email and user ID, the answers are now available in Table 1 - and each answer has an empty field next to it for a "response." I want Airtable to take each "answer", look it up in Table 2, and return the "response" - populating it in the designated field adjacent to each "answer" in Table 1. I cannot do this manually, where I create a link and then go clicking plus signs, finding the answers etc. I need this to just happen. I have found a workaround where I can create an automation triggered on record creation that (1) finds a record in Table 2 matching a field in Table 1 and (2) updates a record in Table 1 based on the result from the first step in the automation, but I would have to do this for every answer - there are 49 answers. When I test it with just two, there is a 5-10 second delay in the automation. Doing that across 49 would kill this process. I am sure there MUST be some easier and more straightforward "Vlookup" style answer here but for the life of me I cannot find it.  Thanks in advance.

[ScottWorld]

Airtable doesn’t natively support VLOOKUP functionality, but the answer is pretty easy:

In Table 1, just convert your 49 answer fields into linked record fields. Then convert your 49 response fields into lookup fields… each one would be based on its associated answer field.

Note that the trick to making this work is that your primary field in Table 2 needs to be the Answer field, because Airtable doesn’t natively support VLOOKUP functionality.

(If, for some unexpected reason, you can’t make your primary field in Table 2 the Answer field, then you would have no choice but to turn to automations to search and link. But Airtable’s native automations can’t do that for you, because they only support up to 25 steps in an automation. You would need to either write your own custom JavaScripts or turn to Make’s Airtable automations.) 

Also, if you’re using TypeForm, you may need to use Make’s TypeForm integrations to get the data from TypeForm into Airtable’s linked record fields by turning on Make’s “Smart Links” option, which lets you send text into linked record fields.

There is a small learning curve with Make, which is why I created this basic navigation video to help. In that thread, I also provide the links to a few other Make training resources there as well. 

If you’d like to learn more about linked record fields and lookup fields, you can check out my free Airtable training course, which you can take for free by signing up for a trial membership with LinkedIn Learning: https://www.linkedin.com/learning/learning-airtable/ 

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld