Learn how to join table ranges in Google Sheets using VLOOKUP & QUERY functions. #join #tables #googlesheets
Пікірлер: 66
@BarryMilne2 жыл бұрын
This awesome! I have been wanting to do joins for ages. Thank you for these great videos.
@EduardoBicelisGarcia2 жыл бұрын
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!
@jermunitz30204 ай бұрын
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 😅
@elielaraujo2 жыл бұрын
Now you solved my problem. I used to drag the lookup formula to all rows. Thanks a lot (from Brazil)
@ExcelGoogleSheets2 жыл бұрын
Fantastic!
@SayantanDas-qy5is Жыл бұрын
Thank you so much! This is a treasure of knowledge indeed!!!
@eArminCom Жыл бұрын
That's amazing!! I could create a report that I was trying to implement using Data Studio in last 3 weeks! Thanks.
@HarshPatel-fr8bv9 ай бұрын
Awesome video man. Learned a great deal from this. Liked and subscribed. TY!
@reforesting2 жыл бұрын
Looks so easy when you do it :D
@ExcelGoogleSheets2 жыл бұрын
:)
@bruno_veloso2 жыл бұрын
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!
@ExcelGoogleSheets2 жыл бұрын
Any of those should work. QUERY(data,"SELECT Col1, Col4, 'My Sheet'",1)
@DinoRodriguez2 жыл бұрын
Unbelievably incredible, a thousand thank you's!
@ExcelGoogleSheets2 жыл бұрын
Glad you enjoyed it!
@RajaGiri_tvpm2 жыл бұрын
Awesome 👍 I am enjoying your Lesson, Thank you
@ExcelGoogleSheets2 жыл бұрын
Glad to hear that!
@yonigolombek33352 жыл бұрын
Wow the query command is powerful! What are its limitations? Does it not run fast, or cannot handle large amounts of data?
@ExcelGoogleSheets2 жыл бұрын
Depends on what you mean by large, but at some point all arrays in Google sheets have size limits, so when you hit the limit you hit the limit.
@hemantsonawane153 Жыл бұрын
watching just one video cleared many of my doubts … thanks bro
@ExcelGoogleSheets Жыл бұрын
Glad to hear
@hamvasq2 жыл бұрын
Your videos are great, thank you much for them!
@ExcelGoogleSheets2 жыл бұрын
Thank You!
@xiukuiji747 Жыл бұрын
Thank you!! Very nice video.
@lpanebr2 жыл бұрын
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.
@SnowHunter442 жыл бұрын
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!
@ExcelGoogleSheets2 жыл бұрын
kzbin.info/www/bejne/jKbLaJ-eZ9Kbh80
@SnowHunter442 жыл бұрын
@@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...
@RTRT-jr8jv Жыл бұрын
very helpful, thank you!
@marcobruni5402 Жыл бұрын
Great, thank you!
@prashanttyagi1156 Жыл бұрын
Sir, your videos is very impressive, I requested you please add also practise files to your video in discription.
@Shanetoodles2 жыл бұрын
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.
@oOoScarecrow31oOo Жыл бұрын
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.
@jozsefolasz87022 жыл бұрын
Cant find the words. Awesome, thank you.
@ExcelGoogleSheets2 жыл бұрын
Thank you!
@oOoScarecrow31oOo Жыл бұрын
Hello, Great video as usual ! Is there a way to do a proper "LEFT JOIN" with google sheets ?
@Meursault669 ай бұрын
Sadly no
@metalgodser Жыл бұрын
This is a bomb! 🔥
@gallowsmere31832 жыл бұрын
Thank you for an excellent video any chance of showing this solution as code ?
@mwKanj2 жыл бұрын
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.
@ExcelGoogleSheets2 жыл бұрын
Use MATCH And NOT functions
@prashanttyagi1156 Жыл бұрын
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
@ashoksoni9337 Жыл бұрын
Thank you sir
@MichaelChu-ty6on Жыл бұрын
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.
@ExcelGoogleSheets Жыл бұрын
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.
@MichaelChu-ty6on Жыл бұрын
@@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.
@GenNextAnalyst2 жыл бұрын
Holy! Thanks man!
@ExcelGoogleSheets2 жыл бұрын
:)
@yonigolombek33352 жыл бұрын
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
@ExcelGoogleSheets2 жыл бұрын
Check these out kzbin.info/www/bejne/hYfdiaOCf7eWh80 kzbin.info/www/bejne/pmarkKuOpKyoidU
@moses54072 жыл бұрын
So, is it correct that a nested formula based on this could join multiple tables?
@ExcelGoogleSheets2 жыл бұрын
Basically you just need to repeat the VLOOKUP part for more tables. Depending on your next join key it can be fairly simple or quite complicated.
@moses54072 жыл бұрын
@@ExcelGoogleSheets thanks!
@Kevin_Cohen2 жыл бұрын
Please post way more! I already seen every video 😫
@ExcelGoogleSheets2 жыл бұрын
All 400 of them?! Wow!
@Kevin_Cohen2 жыл бұрын
@@ExcelGoogleSheets every single google sheets one yes!
@ExcelGoogleSheets2 жыл бұрын
I have a question then :) What is left that I have not covered yet? :)
@Kevin_Cohen2 жыл бұрын
@@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!
@Nowheredotcom7 ай бұрын
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
@RahulSharma-uj7cr2 жыл бұрын
We are not able to see your previous video which was your live session. it is showing as private
@ExcelGoogleSheets2 жыл бұрын
Parts of it will be edited and posted for better user experience.
@jaykkyawal22152 жыл бұрын
Not working for me, I mean i can not get the array columns to display except column of vlookup