Hi - I have a question for all the excel/Google sheet whizes here.
I’m trying to replicate the function of the relation column in Google sheet. Basically, I want to use a VLOOKUP function to return all the matching values from a different table + I need it to work with arrayformula (or a similar function that will allow me to have the vlookup work across all rows).
Does anyone have any suggestions?
Thanks!
Yes, VLOOKUP and ARRAYFORMULA will do that.
Have you tried it?
Where are you getting stuck?
If I do vlookup it would return only the first match but I need to return all the matches.
So I tried to use this formula:
=transpose(filter(‘Table1’!U2:U,‘Table1’!C2:C=A2))
But it matches all the values in Table1 only to cell A2
If I do this formula it doesn’t work at all (I get n/a)
=transpose(filter(‘Table1’!U2:U,‘Table1’!C2:C=A2:A))
Basically I want to retrieve all the values in Table1/column-U that match the condition (the last part of the filter function).
Do you know in advance an upper limit of how many results can be there in the lookup?
I’ve used a query to pull back multiple matches, but it’s not arrayformula friendly which makes it hard to work with and it uses a lot of sheet processing time if you have a lot of rows.
This is the formula I used to use to build dynamic html tables. I’ve since converted it to do everything within glide. The Concatenate with the Query is what joins everything together.
=IF(LEN(C10)=0, "", CONCATENATE("<table width=100%>" , query(Lessons!A$2:L, "select K where A='" & A10 & "' and E='" & B10 & "' and J>= date'" & TEXT(D10, "yyyy-mm-dd") & "' and J<= date'" & TEXT(E10, "yyyy-mm-dd") & "' order by A, D ",0), "</table></br><div style=""text-align: right"">TOTAL: ", DOLLAR(G10, 2), "</div>"))
I have to wonder though if you would have some process in your app where you would already have a Joined List column of your data, then through some action tiggered by the user, you could write that joined list to a sheet column. That would be a lot easier, in my opinion, instead of trying to do it directly in the sheet with sheet formulas.
Nowadays with formulas that I can not use in Glide and not arrayformula friendly (let’s say GoogleFinance), I use a script to write that specific formula to any new rows created in the Sheet. The formula is kinda like yours. For example:
=IF(ROW(A1)=1, "Company", IF(A1="",,GOOGLEFINANCE(A1, "name")))
The script looks like this.
function StockRefresh(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//checks that we're on the correct sheet.
if(sheet.getSheetName() == "Stock Data") {
this.arrayThis("B1:B");
}
function arrayThis(range){
SpreadsheetApp.getActiveSpreadsheet().getRange(range).setValue(SpreadsheetApp.getActiveSpreadsheet().getRange(range).getCell(1,1).getFormula());
}Ok. Thanks guys. I’ll look into it.
Trying to do this as well… would it be better to do a formula or script?
I have Business Name and Featured Photo in the Master Sheet and I would like to ring those two column over to User Profiles Sheet, based on the email address that is displayed in the row. It should match a row in the Master Sheet, and pull over the business name and featured photo tied to that row.
Sounds like you can do that with an Arrayformula and Vlookup?
I have this on another sheet, would it be something like this?
={“Email 1”;ArrayFormula(if(len(C2:C),VLOOKUP(C2:C,{‘Master Sheet’!B2:B,‘Master Sheet’!C2:C},2,0),""))}
And if the data on the Master Sheet is edited, that changes i this second sheet as well correct?
Yes, and you can actually return multiple columns using just one vlookup.
OK, let me see what each part of the formulas calls for again.
I think I figured it out…two things however:
1-Some emails have more than one business attached to them, I was thinking of bringing over the Business Row ID, but I remember there is the thing about when rows are deleted on the master sheet then everything gets messed up.
2- A chat can have 1 out of up to 5 different businesses tied to one email address. I can find out which business by looking at the chat sheet, but I was thinking about how to bring that over and how that will look, or if it would work.
I am also not sure if I should change the 2,0 portion of the formula.
Master Sheet
Chat Sheet
User Profile Sheet- Trying to bring over Business Row ID, Business Name and Featured Image.
Vlookups wil only return one match. How do you want these cases to work?
This is for the comments component within the chat, so the correct name of business and featured photo is displayed. I don’t know if I can get the component to use another sheet other than user profiles for the displayed data.
I think this is the right place for my question. I have one tab in GSheet with a list of businesses and associated information columns (name, person who referred, category, address, biz contact info, URL). In a second tab called “Category List” in GSheet there is a lists of all the types of businesses (restaurant, grocery store, bakery, etc.) They correspond to the categories for each business in the previous sheet.
The data from both tabs in the sheet pulls into Glide table as it should. In GTable I set up a relation column in both tables (Business and Category List).
When I relate the category column in the Business table to the category in the Category List table for some reason the Category List pulls all of the names in the “Who referred” column in the Business Table.
I copied all of the setting from a pervious app I built all in GTables and it works perfectly. The goal is to have a tab in the app that lists all of the categories and when you click on a category you are presented with a list of the businesses that fall under it. ie: Mobile tab for Category List > Restaurants > all the restaurants.
Any idea why this works in my Glide Table only version but the “Who referred” data shows up in Category Table when I do the same in a GSheet version?
Thank you!
Is the answer to my question that I should set up the relation of Category in the tabs in GSheets first instead of doing it in Glide?
If so, can you share a link to instructions on how to do that?
If you’re referring to the thing that shows up in the relation column, it’s just a column that Glide picks from the data to show you that a relation is there to be used, it’s not the actual data that is returned.
Think of it as a pathway to those rows, if you want to return actual data, use a lookup or a joined list column on top of that relation.
Thanks for the reply. I’ve added some screen shots for clarification. In the image of the Category List tab notice that the Cat Relation is displaying the names of people who referred the company. In the GTable version there is an icon or image of each business that has the corresponding category name. ie: 10 restaurants results in 10 icons/images in the Cat Relation field that corresponds with the restaurant category.
In the app under a category tab that pulls from Category List I get no results when I click on a category. In the GTable version I get a list of all the restaurants when I click on the Restaurant icon under categories in the app.
Any other ideas? Thanks again!
Based on your screenshots, to get that all you need is an inline list component that uses the Category List → Cat Relation column as its source.
I got it fixed. The tile of the items on the in app tab was set to the referring person’s name instead of the business name they referred. Palm on forehead.
Thanks again!
