Vlookup Style Search in the Same Table

[Dallas_Lindauer]

I have a table of tasks that looks something like this:

Task Name	Owner	Deadline	Dependencies
Task1 		Ashley	3/2/2018 	none
Task2 		Bill 	3/4/2018	Task1
Task3 		Cody 	3/5/2018 	Task1

I want to add annother column called ‘Dependency Deadline’. For Task2 and Task3 the Dependency Deadline should be 3/2/2018. The goal here is to flag a task that has a deadline before one of its dependencies. I’ve tried using the lookup feature but it requires a Link to a different table, not the same table.

In excel this would do the trick:
=VLOOKUP(D1, A:A, 3, FALSE)

Any ideas would be appreciated. Thanks!

[W_Vann_Hall]

As best I know, you’ll have to use a second table. This second table will have only a single record, but every record in your main table will need to link to it——

Actually, it’s essentially the same method as described in this reply from a couple of days ago. (My apologies; I’m feeling a little lazy today.) The specifics will be a little different, of course — but here’s a demo base that implements what you need. The only change you’ll need to make to your processes is that whenever you create a new record you’ll have to link it to the [Dependency] table. That’s a two-click action: Click 1 on the plus sign in the {Link to Dependency} field; Click 2 on the single field in [Dependency].

If you have any questions, please ask!

Airtable

📆 Dependency Deadline demo - Airtable

Explore the "📆 Dependency Deadline demo" base on Airtable.

[Lee_P]

@W_Vann_Hall, Can you help with a workaround where a VLOOKUP would be ideal:

• We have an airtable with 1000 people in 1000 rows. Each person has an email address. Some of the people have a phone number in the phone number column
• We have an excel sheet that has the email addresses AND the missing phone numbers

I’d like to create a way to use the email address as a unique ID and add the phone numbers where they are missing.

Is there a way to do this (without entering them one-by-one or linking tables one-by-one)?

I don’t want to can’t export, vlookup in excel, and then import because there may be lost activities and linkages with other tables.

[Justin_Barrett]

If I may offer a possible solution, I think the following will work:

First off, here’s a dummy [People] table I mocked up with names, emails, and partially-filled phone numbers:

Export the Excel data as a .csv, then import that into a new table in Airtable. I’ll call this [Email2Phone]. Make sure the email addresses are in the primary field, as this will associate each email with the proper phone number. Here’s a quick mockup table I made with that in mind. Notice that the emails and numbers are not in the same order as in my existing table:

Back in your existing table ([People] in my sample), change the field type of the current email field to a Link, and point it at the [Email2Phone] table. This should keep the existing data, but just change everything from literal text to links to the associated records from the [Email2Phone] table:

Looking in the [Email2Phone] table, there should now be a {People} field that looks something like this:

Back in [People], change the {Phone} field to a lookup, using the {Email} links to pull the related phone numbers from the [Email2Phone] table:

That will populate the phone number field with all of the phone numbers matching the linked emails.

Now just reverse the last two steps. Change the {Phone} field back to the phone type, then change the {Email} field back to the email type. In both cases, Airtable will retain the data, so you’re left with this:

You can now remove the [Email2Phone] table, as it’s no longer needed.

[JonathanBowen]

Ingenious @Justin_Barrett!

[Lee_P]

Wow. @justin_barrett is an airtable badass. Thanks!

[Justin_Barrett]

Thanks, but I think I was just recalling a similar solution I saw from someone else, very possibly @W_Vann_Hall

[Noah]

Hi Justin,

I read through the above several times - thank you so much for sharing this!
My use-case is slightly different though and I can’t seem to figure out how to get the results that I’m looking for - was hoping you had some advise!

I imported 3 tables into Airtable:

1. Apartment Listings (Primary field is address, secondary field has a unique ID for each apartment)
2. Brokers (Primary field is the brokers name, secondary field has a unique ID for each broker)
3. Broker Assignments (Primary field is the Apartment Listing ID from above, secondary field is the corresponding Broker ID

Most of the spaces have several brokers listed on them, so on the Broker Assignments table, the same space is often listed 2-3 times with the corresponding broker. Similarly, there are brokers who are assigned to multiple spaces and if you sort by Broker ID, there could be 5-10 spaces that line up with them.

What I can’t seem to figure out is how to bring all of this information on to the first table (Apartment Listings), so that I have a field that lists all of the assigned brokers for each space.

Does this make sense?

Thanks so much in advance!

Noah

[ScottWorld]

@Noah

You’re almost there.

First, you would actually want to temporarily make the Apartment ID the primary field in the Apartment Listings database. This will require you to copy & paste columns of data to move things around in your system.

That’s because when you’re linking from a field in one table (“Table B”) to another table (“Table A”), Airtable only links to the data in the Primary Field in that other table (“Table A”). Unlike other database languages, Airtable doesn’t give you any other options for linking.

But here’s what’s cool — you only need to change the primary field temporarily. Once everything is linked, you can change the primary field back to whatever you want and Airtable will still remember the linkings!

So anyways, once you’ve done that:

Go back into your brokers table (“Table B”), customize the Apartment ID field to be a “link to another record” and choose your Apartments table (“Table A”).

That’s it! You’re done! Once you do that, Airtable automatically links the 2 tables together (brokers & apartments).

Back in your Apartments table, a brand new field was created that shows all the brokers that are associated with each Apartment. IF YOU DON’T SEE THIS FIELD, Airtable has automatically hidden it for you — and you will find it under the “Hidden Fields” button.

And if you have multiple brokers per apartment, it will show you all the brokers per apartment! Just like you wanted!

(What’s also kinda cool is that the apartments table will display the broker’s names for you — as opposed to their Broker ID or their email address or any other information — since you already made “broker’s name” the primary field in the brokers table.)

[Noah]

@ScottWorld thanks for this detailed breakdown - I so very much appreciate it.
Maybe you could lend some guidance one last workflow issue that I’m looking to solve.

Through some additional linking, rollups, and changes, I now have the set-up in Airtable that I want: 2 tables - 1 for the apartments (with the listing brokers linked in a column), and 1 for the brokers (with the spaces that they have listed Linked in a column on that board).

The final issue I’m looking to solve centers around which apartment listings show up next to a brokers name (on the brokers table). I have a column on the apartments table labeled “archived” - and any time a space is taken off of the market, I select “Yes” in the Archived column for that space. My main view on the apartments table uses a filter to exclude any spaces that have been archived. Unfortunately, this does not correlate to the brokers table - when I click on a brokers name, it shows me all the apartments linked, regardless of whether they are archived or not.

I looked further into the “Limit Record Selection to a View” option on the Link to record column on the Broker’s table and I selected the view on the Apartments board in which the Archived spaces are hidden - but it doesn’t appear to be changing anything.

I found this support piece and at the bottom is mentions two things to keep in mind:
A couple of other things to note about this feature:

• If you toggle on Limit linked record selection to a view in a field where there are already linked records, this will not have any effect on the existing records.
• This feature does not have any effect on any new linked records that are created using the linked record picker.

I believe the first bullet point explain why this isn’t working - without recreating the entire base again/switching apartment addresses with ID’s and back again, any idea as to how I can quickly get this working?

As always, I greatly appreciate any and all of your help and input here!

Thanks,
Noah