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.
