Google Sheets - Join Tables using VLOOKUP & QUERY Functions

[@EduardoBicelisGarcia]

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!

[@jermunitz3020]

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 😅

[@BarryMilne]

This awesome! I have been wanting to do joins for ages. Thank you for these great videos.

[@JBpierre67]

Really good content! Thanks for the work you put into this ! 👍

[@elielaraujo]

Now you solved my problem. I used to drag the lookup formula to all rows. Thanks a lot (from Brazil)

[@ExcelGoogleSheets]

Fantastic!

[@SayantanDas-qy5is]

Thank you so much! This is a treasure of knowledge indeed!!!

[@hemantsonawane153]

watching just one video cleared many of my doubts … thanks bro

[@ExcelGoogleSheets]

Glad to hear

[@lpanebr]

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.

[@reforesting]

Looks so easy when you do it :D

[@ExcelGoogleSheets]

:)

[@yonigolombek3335]

Wow the query command is powerful! What are its limitations? Does it not run fast, or cannot handle large amounts of data?

[@ExcelGoogleSheets]

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.

[@bruno_veloso]

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!

[@ExcelGoogleSheets]

Any of those should work. QUERY(data,"SELECT Col1, Col4, 'My Sheet'",1)

[@eArminCom]

That's amazing!! I could create a report that I was trying to implement using Data Studio in last 3 weeks! Thanks.

[@HarshPatel-fr8bv]

Awesome video man. Learned a great deal from this. Liked and subscribed. TY!

[@RajaGiri_tvpm]

Awesome 👍 I am enjoying your Lesson, Thank you

[@ExcelGoogleSheets]

Glad to hear that!

[@xiukuiji747]

Thank you!! Very nice video.

[@DinoRodriguez]

Unbelievably incredible, a thousand thank you's!

[@ExcelGoogleSheets]

Glad you enjoyed it!

[@SnowHunter44]

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]

kzbin.info/www/bejne/jKbLaJ-eZ9Kbh80

[@SnowHunter44]

@@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...

[@hamvasq]

Your videos are great, thank you much for them!

[@ExcelGoogleSheets]

Thank You!

[@RTRT-jr8jv]

very helpful, thank you!

[@Shanetoodles]

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.

[@ashoksoni9337]

Thank you sir

[@prashanttyagi1156]

Sir, your videos is very impressive, I requested you please add also practise files to your video in discription.

[@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

[@marcobruni5402]

Great, thank you!

[@jozsefolasz8702]

Cant find the words. Awesome, thank you.

[@ExcelGoogleSheets]

Thank you!

[@moses5407]

So, is it correct that a nested formula based on this could join multiple tables?

[@ExcelGoogleSheets]

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.

[@moses5407]

@@ExcelGoogleSheets thanks!

[@oOoScarecrow31oOo]

Hello, Great video as usual ! Is there a way to do a proper "LEFT JOIN" with google sheets ?

[@Meursault66]

Sadly no

[@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.

[@metalgodser]

This is a bomb! 🔥

[@gallowsmere3183]

Thank you for an excellent video any chance of showing this solution as code ?

[@GenNextAnalyst]

Holy! Thanks man!

[@ExcelGoogleSheets]

:)

[@yonigolombek3335]

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

[@ExcelGoogleSheets]

Check these out kzbin.info/www/bejne/hYfdiaOCf7eWh80 kzbin.info/www/bejne/pmarkKuOpKyoidU

[@mwKanj]

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.

[@ExcelGoogleSheets]

Use MATCH And NOT functions

[@Kevin_Cohen]

Please post way more! I already seen every video 😫

[@ExcelGoogleSheets]

All 400 of them?! Wow!

[@Kevin_Cohen]

@@ExcelGoogleSheets every single google sheets one yes!

[@ExcelGoogleSheets]

I have a question then :) What is left that I have not covered yet? :)

[@Kevin_Cohen]

@@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!

[@RahulSharma-uj7cr]

We are not able to see your previous video which was your live session. it is showing as private

[@ExcelGoogleSheets]

Parts of it will be edited and posted for better user experience.

[@Nowheredotcom]

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

[@jaykkyawal2215]

Not working for me, I mean i can not get the array columns to display except column of vlookup

[@jaykkyawal2215]

Okay it worked, only If i add Arrayformula

[@ExcelGoogleSheets]

Cool!

[@metalgodser]

You are the fking goat, man

[@Puner54]

Teacher...my Brain hurts

[@ExcelGoogleSheets]

:)