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

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.

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

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

@ExcelGoogleSheets

2 жыл бұрын

Fantastic!

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

Unbelievably incredible, a thousand thank you's!

@ExcelGoogleSheets

2 жыл бұрын

Glad you enjoyed it!

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.

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

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 😅

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

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

2 жыл бұрын

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

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

@ExcelGoogleSheets

Жыл бұрын

Glad to hear

Your videos are great, thank you much for them!

@ExcelGoogleSheets

2 жыл бұрын

Thank You!

Awesome 👍 I am enjoying your Lesson, Thank you

@ExcelGoogleSheets

2 жыл бұрын

Glad to hear that!

very helpful, thank you!

Great, thank you!

Thank you!! Very nice video.

Looks so easy when you do it :D

@ExcelGoogleSheets

2 жыл бұрын

:)

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

2 жыл бұрын

kzread.info/dash/bejne/iKmcmc6hYtDOgc4.html

@SnowHunter44

2 жыл бұрын

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

Cant find the words. Awesome, thank you.

@ExcelGoogleSheets

2 жыл бұрын

Thank you!

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

Holy! Thanks man!

@ExcelGoogleSheets

2 жыл бұрын

:)

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

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

@ExcelGoogleSheets

2 жыл бұрын

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.

This is a bomb! 🔥

Thank you sir

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

2 жыл бұрын

Check these out kzread.info/dash/bejne/gYquutKFerXJgc4.html kzread.info/dash/bejne/oml8wdqRn6rbg9Y.html

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

@Meursault66

8 ай бұрын

Sadly no

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

2 жыл бұрын

Use MATCH And NOT functions

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

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.

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

@ExcelGoogleSheets

2 жыл бұрын

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

2 жыл бұрын

@@ExcelGoogleSheets thanks!

Please post way more! I already seen every video 😫

@ExcelGoogleSheets

2 жыл бұрын

All 400 of them?! Wow!

@Kevin_Cohen

2 жыл бұрын

@@ExcelGoogleSheets every single google sheets one yes!

@ExcelGoogleSheets

2 жыл бұрын

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

@Kevin_Cohen

2 жыл бұрын

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

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

@ExcelGoogleSheets

2 жыл бұрын

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

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

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

@jaykkyawal2215

2 жыл бұрын

Okay it worked, only If i add Arrayformula

@ExcelGoogleSheets

2 жыл бұрын

Cool!

Teacher...my Brain hurts

@ExcelGoogleSheets

2 жыл бұрын

:)