Sir, I found that arrayformula can not use together with filter function, because both of them are about array. I many case, we know that vlookup can refer to first value or last value (if we combined that arrayformula and vlookup with sort function). So, in short sentence, we can use vlookup if we just want to lookup for the first or last value. But, my question, how if we have 3 line of same reference data (same id, for an example), and we want to refer to the second line. It can be done with filter and index function, but how if we want make it automatically drop that vlookup like with arrayformula Sir?
Depends on how many non matching values you have. If there is always a match then it shouldn't make any difference, since VLOOKUP will search until it finds the first match.
Really appreciate your tutorials & I have followed many, thank you! - I have used the above formula but when I'm bringing over separate Date & Time columns the format changes to numbers - how can I integrate the formatting for Date & Time for this VLOOKUP formula please?
@@ExcelGoogleSheets Thank you for your quick response however, I have done that many times but on next data entry it always reverts back to numbers! I ideally need the formula to set the Date & Time formatting in below formula & dont know how? =ARRAYFORMULA(IF(B2:B="","", VLOOKUP(B2:B,{'Form responses 2'!W2:W,'Form responses 2'!C2:E},MATCH({"Sample Date","Sample Time","Priority"},'Form responses 2'!C1:1,0)+1,0)))
=QUERY(ARRAYFORMULA(IF(B2:B="","", VLOOKUP(B2:B,{'Form responses 2'!W2:W,'Form responses 2'!C2:E},MATCH({"Sample Date","Sample Time","Priority"},'Form responses 2'!C1:1,0)+1,0))),"SELECT Col1, Col2, Col3 format Col1 '$0.00',0) Watch QUERY format and label videos for more details.
Hi! Your tutorials are amazing! I've learned so much in the last year or so! I have a special request in the matter of working with large arrays: Would you please give me a hint how to kind of "transpose" a large matrix with 108 rows and 76 columns? The word "transpose" is not in fact the most appropriate in my case. I would like, starting from my matrix, which is populated with every valid combination of Accounts and Subaccounts from a General Ledger transaction batch, to generate a list of 8208 rows, I mean each and every valid Account-Subaccount combination to be displayed in a list of 8208 rows and one column. Thank you very much.
How to do this: I want the text from the adjacent cell to appear automatically If I call one cell as a reference. That is I want the content of the B2 cell to appear if I refer A1 cell. For eg. say A1 is having a date "Jun 01, 2021" and B1 has the text "English", I want to refer the A1 cell and want to display the B1 cell content, like C2 = if A1 = "Jun 01, 2021" then it is equal to B2 and print B2. I have OCD and this one is killing me... Please help with formulae. SO I can automatically make my subjects appear on the specific dates on the dates calendar I have created
Since you're on an array formula series you might want to address an annoying thing that happens and we try to use the AND and OR functions and find that they don't work and instead we need to use + and * with booleans true/false.
Hi, your way of teaching is very good, but I stuck somewhere when I need to extend formula, I need to add multiple ifs for blank check Please shorten this formula =IF(IF($F7="","", VLOOKUP($F7, Sheet1!$D$110:$Q$301, 4, FALSE))="","",IF($F7="","", VLOOKUP($F7, Sheet1!$D$110:$Q$301, 4, FALSE))+I$3)
Hmm and yet nothing I needed:)) Just a simple VLOOKUP Table where I can see the Types and total amount... yep... total price for Training Shoe. In my case my shop has lots of different items. I want to know how many units and how much of that type I sold, so I want a VLOOKUP table where I put the Type, and it would give me in 2 columns Total Units, Total Cost, Total Profit.
Your content is for the Pros! Thank you for putting out such great resources for free. You are doing the world a great service!
I appreciate that!
This is exactly what I need! Thank you!
Wow. Just wow. I thought u gotta use index(match) there. Really nice trick for the virtual table one. Super cool.
Great! New knowledge!
Excelente!
Oh my god... I thought i was good at Google Sheet but you're clearly my master ! thanks ++ I'll share you chanel to my friends
Thanks :) Welcome aboard!
Nice tutorial!
Great video!
Sir, I found that arrayformula can not use together with filter function, because both of them are about array.
I many case, we know that vlookup can refer to first value or last value (if we combined that arrayformula and vlookup with sort function). So, in short sentence, we can use vlookup if we just want to lookup for the first or last value. But, my question, how if we have 3 line of same reference data (same id, for an example), and we want to refer to the second line. It can be done with filter and index function, but how if we want make it automatically drop that vlookup like with arrayformula Sir?
Thank you
Excelent
👏👏👏👏👏👏👏
hi, first thx for your videos. small question : Does B2:B10 vs B2:B has an impact on speed with a very large spreadsheet?
Depends on how many non matching values you have. If there is always a match then it shouldn't make any difference, since VLOOKUP will search until it finds the first match.
I want to apply array and match function to multiple google sheet and vlookup . How can I do that. Please guide
Thanks
This content is advanced level. Would be great if you could share the data sheet link so that we could practise along
Would be good if you posted the other video's you've mentioned - in your video description as well
ruclips.net/video/Z_OQr8U7RE0/видео.html
Really appreciate your tutorials & I have followed many, thank you! - I have used the above formula but when I'm bringing over separate Date & Time columns the format changes to numbers - how can I integrate the formatting for Date & Time for this VLOOKUP formula please?
Yuo could wrap it in a QUERY function and format your columns, but why not just format the column manually?
@@ExcelGoogleSheets Thank you for your quick response however, I have done that many times but on next data entry it always reverts back to numbers!
I ideally need the formula to set the Date & Time formatting in below formula & dont know how?
=ARRAYFORMULA(IF(B2:B="","", VLOOKUP(B2:B,{'Form responses 2'!W2:W,'Form responses 2'!C2:E},MATCH({"Sample Date","Sample Time","Priority"},'Form responses 2'!C1:1,0)+1,0)))
=QUERY(ARRAYFORMULA(IF(B2:B="","", VLOOKUP(B2:B,{'Form responses 2'!W2:W,'Form responses 2'!C2:E},MATCH({"Sample Date","Sample Time","Priority"},'Form responses 2'!C1:1,0)+1,0))),"SELECT Col1, Col2, Col3 format Col1 '$0.00',0)
Watch QUERY format and label videos for more details.
@@ExcelGoogleSheets you are simply amazing, Im subscribed THANK YOU!
Thank
Hi! Your tutorials are amazing! I've learned so much in the last year or so! I have a special request in the matter of working with large arrays: Would you please give me a hint how to kind of "transpose" a large matrix with 108 rows and 76 columns? The word "transpose" is not in fact the most appropriate in my case. I would like, starting from my matrix, which is populated with every valid combination of Accounts and Subaccounts from a General Ledger transaction batch, to generate a list of 8208 rows, I mean each and every valid Account-Subaccount combination to be displayed in a list of 8208 rows and one column. Thank you very much.
I'm not sure. If you share and example sheet with input->output example I might find a solution.
@@ExcelGoogleSheets Okay. I have an example sheet ready to share. Which email? The one starting with chicago at gmail?
Please share here in comments. Emails are not read for security reason.
@@ExcelGoogleSheets docs.google.com/spreadsheets/d/1DudA9pkYOrvfFwDhgsiGoXmVPsch3txdwyjYPOu28ZI/edit?usp=sharing
Not sure how large of a dataset it will support, but I have an example in your sheet.
Thank you so much.... sir,...
:)
How to do this: I want the text from the adjacent cell to appear automatically If I call one cell as a reference. That is I want the content of the B2 cell to appear if I refer A1 cell. For eg. say A1 is having a date "Jun 01, 2021" and B1 has the text "English", I want to refer the A1 cell and want to display the B1 cell content, like C2 = if A1 = "Jun 01, 2021" then it is equal to B2 and print B2. I have OCD and this one is killing me... Please help with formulae. SO I can automatically make my subjects appear on the specific dates on the dates calendar I have created
Since you're on an array formula series you might want to address an annoying thing that happens and we try to use the AND and OR functions and find that they don't work and instead we need to use + and * with booleans true/false.
I'm pretty sure I've already covered this, just not sure in which video :)
It’s covered in this video at 8:30
ruclips.net/video/ndxOEbgeqoQ/видео.html
Sir, thank you, but why it does not work for me?..i writed all the formula, but only one result that come up..
Hello sir I want to this sheet for practice...
Is there a file associated with this tutorial?
Hi, your way of teaching is very good, but I stuck somewhere when I need to extend formula, I need to add multiple ifs for blank check
Please shorten this formula
=IF(IF($F7="","", VLOOKUP($F7, Sheet1!$D$110:$Q$301, 4, FALSE))="","",IF($F7="","", VLOOKUP($F7, Sheet1!$D$110:$Q$301, 4, FALSE))+I$3)
Hmm and yet nothing I needed:)) Just a simple VLOOKUP Table where I can see the Types and total amount... yep... total price for Training Shoe. In my case my shop has lots of different items. I want to know how many units and how much of that type I sold, so I want a VLOOKUP table where I put the Type, and it would give me in 2 columns Total Units, Total Cost, Total Profit.
Sounds like you need a Pivot Table
Thanks
👍