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!

[ScottWorld]

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.

[Noah]

@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

[ScottWorld]

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.

[Noah]

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

[ScottWorld]

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.

[ScottWorld]

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:

Launched: conditional relational fields (lookup, rollup, and count) Announcements

You can now add custom filter conditions to lookup, rollup, and count fields to limit which records are included in the results. Here’s how to do it: Create a valid lookup, rollup, or count field (if you don’t already have one in your table, you’ll need to create a valid linked record field first). In the field customization menu, turn on the “Only include records which match certain conditions” toggle. Specify the relevant filters for your use case. [Screen Shot 2020-04-28 at 10.50.22 AM] […

[Noah]

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

[ScottWorld]

Haha, you’re welcome! :slightly_smiling_face: