VLOOKUP without a script

[AmyL]

Hello,

I'm wondering if anyone has any recommendations on how to achieve the use case below. I've seen VLOOKUP scripts and I've also seen automations, but not sure which would be easier given the criteria.

Table A has each Product and State pair listed as its own row (as there are other columns of data applicable to that pair). The Product will have as many rows as necessary, depending on how many states it's sold in. The Status column says if it's approved to be sold in that state.

Table B needs to be a summary of all of the states that each Product is sold in and the status of the approval, where each Product has one row and the States are now listed as columns.

Table B needs to lookup the Product name in Table A, find the State name in each row and bring the Status in that row over to the matching State column in Table B.

It would be great if each time a record is created in Table A, a single record representing that product is created in Table B. 

So it would look like this:

Thoughts?!

Ya'll and your brilliant minds are much appreciated! 🙂

[pressGO_design]

Option A: It’s vital that the data be laid out *in Airtable* exactly the way your screenshot shows above. 
Solution: try using the Pivot Table extension in a Dashboard? 

Option B: You need to hand off the data *outside of Airtable* like in Google Sheets or Excel. 

Solution: Since you already have a States table that is linked to the Products table, create a view in that table with conditional lookup columns so that you only see the statuses for Product A in the Product A column and the statuses for Product B in the Product B column.

The hiccup is that view is the opposite of what you want. It has the States as rows and the Products as columns. So you export that view into a CSV, open it in whatever your using, and transpose the data so that it looks exactly like your screenshot. 

[AmyL]

Hello,

Thanks for the ideas but I'm not sure that either option will work for me. When I create the pivot table, I can only get a count of the statuses and not display the actual value.

For the States table, there are already several columns in that table so adding one per product (which will end up being a very large product set) also doesn't make sense. It's not possible to do something similar to Vlookup in Excel with the conditions I mentioned above? Without writing a script, I mean?

[pressGO_design]

It's completely possible to do something similar to Vlookup in Excel with the conditions you mentioned above. The conditional lookup columns in your States table are the Vlookups.

I'm not sure I understand why it doesn't make sense to add more columns in your States table. Airtable allows for up to 500 columns per table. You don't need to see all of those columns at once: you create views to access the information you want to see in the format you want it to be in.

Having said that, I'm wondering if the way your data is structured is getting in the way of you being able to do what you want to do. Obviously, that's just a guess based on your screenshots.

[AmyL]

Thanks for your response. If I add the Product columns to the States table, how do I go about only pulling back the Status field for that particular Product/State combo? I'm struggling with the fact that in Excel the formula can be changed for each row while in Airtable it's one formula for that whole column. Do I use the Lookup type column, or a Formula column?

Thanks in advance for your help - I'm really struggling here to replicate what I have in Excel 😞 Appreciate any time you're taking to help me!

[pressGO_design · Accepted solution]

Trying to replicate what you have in Excel is making your life harder. Excel isn’t a database. Airtable isn’t a spreadsheet. There’s a lot of functional overlap between the two, but you’re going to have a tough time moving to Airtable without understanding the fundamental differences between the two. There’s a training webinar here that you might find helpful.

In your case, using the setup you shared in your screenshots, you would add a lookup column in your States table called “Product A Status” that has the Status field as the thing you’re looking up and a conditional statement to only include records where the Product contains A. Then you duplicate that field and change the conditional statement to products that contain B.