Vlookup Style Search in the Same Table

Ask the community
#1

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!

Vlookup for Airtable?
Roll Ups for Fields Linked Within The Same Table
How do I add a set number of days to a date?
#2

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!

2 Likes
#3

@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.

#4

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:

43%20PM

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:

23%20PM

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:

34%20PM

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

27%20PM

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

44%20PM

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

54%20PM

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:

26%20PM

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

4 Likes
Automatically linking to records in other tables (primary ID keys & foreign ID keys)
#5

Ingenious @Justin_Barrett!

#6

Wow. @justin_barrett is an airtable badass. Thanks!

#7

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

#8

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

#9

@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.)

Importing and linking 3 different tables
Create a formula to match values from two different tables
#10

@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

#11

Yeah, with a more advanced database platform, you would be able to just create a multi-field link between the 2 tables (“show me this broker’s active apartments only”) and this problem would be immediately solved. You would essentially be filtering the records across the link. But since Airtable is a low-code database platform, it’s a tiny bit trickier.

This is what I would do:

In your apartments table, create a new formula field that results in nothing (a blank/empty field) if the apartment has been marked as “archived”. Otherwise, the formula should result in the apartment address. Something like this:

IF({Archived Field}="Archived","",{Apartment Address Field})

Then, back in your brokers table, create a lookup field that points to this new formula field. When setting up your lookup field, you will select the previous link you created to the Apartments table, and then you’ll choose the new formula field as the field.

That will give you a list of only the ACTIVE apartments that are associated with each broker, because it’s essentially ignoring any of the records that don’t have any values listed in their field (i.e. it’s ignoring all the blank/empty fields).

So this is a little bit of a trick — if values are blank or empty, Airtable will ignore them when creating lists for lookup fields.

#12

@ScottWorld
Thanks! This is a neat little workaround and something I’m super happy to know about as I can imagine it will be useful in other use cases moving forward.

The one thing this workaround does not exactly solve - because this is a Lookup column rather than a Link to Base column, the spaces being spit out next to each broker’s name are only text and not expandable to view the information for that space. This gets us 90% of the way there (and is much better than having all of the spaces populate) - I’m hesitant to ask if you know of any workarounds that would take this all the way home?

I appreciate your time and help!

Noah

#14

Yes, you can definitely expand your lookup field to view the information for each apartment. This is functionality that is built natively into Airtable. See #3 below.

However, I can think of 3 different solutions here:

  1. It might be best to view this information from your Apartments table. Either use an existing view or create a brand new view in your Apartments table, then simply group/sort by the Broker Name field. Then, each Apartment will have an entire row to itself (since you’re viewing everything from the Apartments table). It might be nice to create a brand new view for this in the Apartments table, so you can view your apartments by EITHER Apartment name OR by Broker Name, simply by switching back & forth between 2 views.

  2. Staying in the Brokers table: you can increase the row height by clicking on the “row height” button in the toolbar and choosing “Extra Tall”. Note that this button will only give you a maximum of 5 lines per broker, so that may not be enough to view all of the linked apartments. I wish that Airtable’s “row height” button would AUTOMATICALLY EXPAND ITS HEIGHT to show you as much data as necessary per record — I’ve posted this as a feature request here. Please feel free to chime in over there if this is important to you.

  3. Staying in the Brokers table: This is probably the “most elegant option” that you have from the Brokers table, and it’s what Airtable originally envisioned that most users would do. Simply click once into the Linked Apartments field, and then expand the cell to reveal a scrolling list of all the Linked Apartments. You have 2 ways to expand the cell: (1) Either hit SHIFT-SPACE on your keyboard, or (2) click on the little blue “expand” button that appears in the upper right corner of the cell.

There might even be a 4th option of installing an Airtable block (that you could install in the right panel of your database) that would show you all the linked records in a scrollable list. I’m not sure about that — someone else might know if that’s a possibility.

#15

@ScottWorld
Once again, this is all incredibly helpful and I appreciate your taking the time to respond. First things first - I’ve gone ahead and upvoted that feature request. I’m relatively new to Airtable but this is definitely something that I’d find myself looking for sooner rather than later!

Apologies if my previous message was unclear. I went through the steps that you had listed:

  1. Created a new formula field in apartments the displayed the apartments address if it wasn’t archived (left it empty if it was archived) - call this “Archive Formula Column”
  2. Created a lookup field on the brokers’ table that pulled in the information from this new formula field on the apartments table (ie. the non-archived addresses). = call this “Lookup Archive Column”

(For reference, the original linked column on the broker’s table is called “Apartments” - this shows all apartments that a broker is associated with)

As far as expanding records on the secondary table (broker’s) - the “Lookup Archive Column” only pulls in the addresses from the “Archive Formula Column” - those addresses are not “linked” like the ones in the original Apartments column that shows all spaces (active and archived) next to a brokers name. The Lookup Archive Column spaces only show as text - because that’s what they are on the Apartments table.

The tricks that you’ve mentioned - enlarging the row height/ expanding the view of the linked spaces - those are awesome little tricks for any linked column and apply to the original “Apartments” column. Unfortunately, using the Lookup as opposed to the Link to column doesn’t allow our team to see any additional space information.

As for the option to filter the brokers within the apartments table, I hadn’t thought of this and gave it a try!
The small issue with this view is that every apartment has multiple brokers listed on it, and often times a combination of different ones. So when grouping by broker, broker A could be listed consecutively on multiple different lines (Broker A alone, Broker A + Broker B, Broker A + Broker C, etc.) - as it’s picking up each of these groupings of brokers as a different “Listing Broker”.

I can’t say this enough - thank you for all of your help!

#18

I typed up a reply, but I realized that it might be possible to do what you’re looking to do in Airtable… I would probably need to take a look at how you’ve got your tables setup. I think that with some creative use of lookups & filtering, you might be able to accomplish at least one of these things in Airtable, if not both.

#19

OMG, @Noah, you’re not going to believe what Airtable JUST ANNOUNCED 60 MINUTES AGO as a brand new feature in Airtable:

Filtered lookups!!!

Hahahaha!!! I know, it’s true. Amazing.

They’re calling them “conditional lookups”.

Check it out here:

#20

And all we needed to do was have a little conversation about it for this change to happen :slight_smile:
Thanks for bringing this to my attention!

#21

Haha, you’re welcome! :slight_smile: