As someone who uses SQL databases and data frames I find it fascinating how difficult a left join is in Google sheets. The creator did a great job figuring that out 😅
It gets a little slow but sure makes it flexible. If I'm not mistaken one could even add new columns to the original tables and it would keep working as long as the new columns get added in between other columns. Otherwise the referenced ranges do not update.
I am a complete amateur because I don't understand why the 'key' is being shown three times . I guess I should be watching many more of your awesome videos. thank you sir!
Great video. I've avoided arrayformula vlookups with multiple column returns in the past because of performance issues. I think you've even warned against doing it. Anyways, I've opted to just drag down formulas preemptively with the if blank then blank escape to hopefully save on calculations.
Personnaly, I use IFNA to remove NA from the table and the QUERY with "SELECT * WHERE Col1 IS NOT NULL" to remove empty rows and prevent from adding new empty rows in the sheet which is improving the performance.
Awesome, lots of contents in one single video. I have a doubt: I'm often using IMPORTRANGE function and every now and then I've got to bring within IMPORTRANGE a column with a STRING as a constant, for instance the name of the sheet I am importing it from. How do I manage to do that? Creating a custom array with { } or using QUERY? Thanks in advance!
Loving your videos, very helpful. I have a question that may not be directly related to this video, but I will ask it here anyway, hope you understand. I created 37 sheets with historic price data, instead of typing out the sheet name (other) and the cell on the (current) sheet where I want info onto, I created a list of the sheet names in the first column and attempted to use the cell reference to point to the worksheet. What I get is an error code or N/A or unresolved sheet name. =(A2)!B14 where A2 refers to the cell on current page where the other sheet name is listed, and !B14 is the cell on the other sheet I want the info from, as example. Attempted with various quotes etc. Hope you can help!
@@ExcelGoogleSheets thanks for getting back to me - I found the video you did on indirect function and it solved my issue. Seriously looked high and low...
Could you please make a video showing how to use regular expressions within the find and replace function of Google sheets. I often find that this might be useful in correcting formulae that I have written that are not using arrayformulas or lookup tables and therefore I need to change each formula by hand
Sir, I have a google workbook which has sheets with different names, I want to create a single sheet, in which the data of all the sheets is merged. But only the selected number of coloum from all the sheets are required, which should be connected to the drop down list on the main sheet. what is the full query
Very helpful! Thank you very much! I am wondering if there is any way to filter a range using =Filter() formula and the condition is that one column should not contain a list of values; something like =Filter(Sheet1!A:Z , Sheet1!A:ASheet3!$A$1:$A$20). Thank you in advance.
Wondering why you key by "Owners!D2:D" in the formula and not just by " 'Joined Tables'!D2:D' "? otherwise, great video! Was wondering how to achieve a dynamic row count table join with multiple columns (previously used index match) also wondering if there's any implementation with the new XLOOKUP? I was only able to achieve single row multi column or single column multi row joins so wondering if you had a solution for this.
Sorry, don't really remember those details in the video at this point regarding "Owners!D2:D". XLOOKUP doesn't handle dual arrays, at least for now. That's the reason you get only one row. So VLOOKUP is still the function to use for this type of thing.
@@ExcelGoogleSheets Thanks for the response! I figured out the hard way why you couldn't key by 'Joined Tables' since there's a bit of circular dependency. Google sheets suggests the 'solution' of using iterative calculation which i think was too slow with the amount of data I was working with but would probably work fine with less data.
@@ExcelGoogleSheets after watching every single video, google sheets usability, functions were all explained very thoroughly. So in terms of what I think is missing is maybe like “expert” level JS scripting. It’s the only thing I don’t feel advanced in even after watching all your videos. Maybe API integration, and more complex scripting. The rest were really covered in detail!
At 12:22 I get Error Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 6075. Actual: 5574. So up until then worked well but now cannot resolve this think you fail to say that both join datasets need to be equal in row count
