Compare two Tables (VLOOKUP on Excel)



Maybe this is a stupid question, but I’ve been wracking my brains trying to figure it out and couldn’t find a solution.

My Scenario:
Table 1 - list of 300 users with assigned devices by serial number.
Table 2 - list of 1000 devices with other specs, including serial number.

What I am trying to do
Create a third table with all Serial Number listed on Table 1 (I can copy manually) and display on Field 2 the name of the user (record from Table1) and display on Field 3 Other Spec (record from Table2).

On excel, it’s something simple that I can resolve with VLOOKUP (value, table, col_index, [range_lookup]). Any solution/idea for Airtable?

Thanks so much!



You’re going to want to link your records from Table 1 and Table 2 together in some fashion using the {Serial Number} field, which means you want to make the {Serial Number} the primary field in [Table 2] (equipment)

The arrangement you’ve described involves a third table, [Table 3], as a junction table. This might not be necessary in your case. You will need to do one of the following:

/Option 1: Two table arrangement/

  • Make sure the {Serial Number} is the primary field of [Table 2]
  • Convert the {Serial Number} field in [Table 1] into a Link to Another Record field, pointing at [Table 2]'s {Serial Number}
  • Add a new Lookup field pointing at [Table 2] to pull in any other spec info you want from that table

/Option 2: Three table arrangement/

  • Instead of pairing serial numbers to users in [Table 1] and looking up related information in [Table 3], you actually want to do it the other way around. You link records (users to equipment) together in you junction table, and lookup related information in either or both of your “parent” tables if necessary.
  • [Table 3]: Add one Link to Another Record field pointing to [Table 1]
  • [Table 3]: Add one Link to Another Record field pointing to [Table 2]
  • [Table 3]: For each equipment assignment, record it in this table.
  • If you want, go back into [Table 1] and add Lookup fields pointing to [Table 3] to pull the corresponding serial number of assigned equipment. Follow the same process for [Table 2] if necessary.