Hey, thanks!

Yes, that will definitely work and preferred. Naming the ranges based on the column header makes it easier to visually index the query range as an array. I.e. QUERY({q_date, q_store, q_sales}, "SELECT Col1, Col2, Col3 WHERE Col3 > 7.00",1) would naturally state the array index as date(named range) = Col1, store(named range) = Col2, and sales(named range) = Col3 I replicated this same example and gave each column range the names of the headers. The leading "q_" is just my personal naming convention for stating the name is related to using for queries. However, instead of querying the entire range in the array QUERY({Data!A1:G1650}, "SELECT...",1) I called the ranges of concern QUERY({q_date, q_store, q_sales}, "SELECT...",1) E.g. Data!A1:A range is named "q_date", Data!C1:C range is named "q_store", etc. for modular global scope on the ranges to use wherever applicable throughout the workbook including other tabs. I'd even take it a step further and name the range of the entire dataset "Data!A1:G1650" to something I would name a database table like "tbl_customer_sales" so I could reference it in the query range section I.e. QUERY(tbl_customer_sales, "SELECT...", 1) I know it's just not me, but it is definitely easier to build functions with named references rather names of the cell location. I also use this method to JOIN related data from other tabs rather than using the arrayformula({vlookup}) method.

Sure.

@@navigatormoore I find it much easier to work with named ranges when using query also, even if just to define the database range. Another nice benefit is that Sheets will autocomplete the range names. I like your "q_" prefix for that reason too, since you can effectively call up all your query column options by typing the prefix. I'm going to start using that too, so thanks for the idea.

Should work just fine.

Have the same issue here. Excel has more visibility on data structure between files. Sheets with such functional as deadly weapon in a newbie hands

i’ve started building data index tabs where I list the name of the tab, it’s purpose, and where the original source data lives. To your original point, data hygiene best practices is an important conversation to have

@@mattbrigidi I just started that also but the hard part is that I'm not the only one who touch the main source of data because we run at the company level so sometime it is hard to force everyone to make an update on the index table once they make changes.

You'll need FILTER TODAY MONTH functions.

Because the data type is inconsistent of that particular column in data set.

:)

U did it! Thanks!

No, you would need to use a different technique.

@@ExcelGoogleSheets omg, so glad you answered, thank you! I was thinking doing an import range separatey to all 3 of my sources I am trying to merge and then query them with arrays within my spreadsheet. Do you have a better solution for this :o ?

If you have matching column names, you should be able to search and find the column.

@@ExcelGoogleSheets dont know if it helps now but I managed to do it with concat within the arrayformula and a textjoin before the arrayformula all in the same formula. Dont know if its too complicated for what I am trying to do but it worked 🤷🏻‍♂️ jajajaja

Yes.

Nice catch😁

I do cut videos, however in this instance it didn't affect the response time. That's how it works when you have fast enough internet connection and a fast enough computer without having too much going on in the spreadsheet.

@@ExcelGoogleSheets even this answer not for my question, but it was long time not seen your personal anwer like this appear in my notification pop up. I think I little miss it :)

@@ConsulthinkProgrammer I try to always answer comments as time allows.

@@ExcelGoogleSheets Thanks a lot Sir. I learn so much from you. I use it in my work and documenting it in my channel, to record what i've learn (almost) from you and some from other youtube channel :)

You forgot to make it public.

@@ExcelGoogleSheets docs.google.com/spreadsheets/d/1ROiZhKaEbjZMBjs0pA1Jg1vt3nQyNsptz2_tznykpPs/edit?usp=sharing

OK, so the reason you are having these issues is because on your Sheet1 names in the first column have an extra space after the last name.

@@ExcelGoogleSheets thx. i would not have even thought of looking there.