Learn how to create advanced VLOOKUP functions using Google Sheets arrays. #vlookup #googlesheets #advanced #arrays
Пікірлер: 51
@Avi80533 жыл бұрын
Your content is for the Pros! Thank you for putting out such great resources for free. You are doing the world a great service!
@ExcelGoogleSheets3 жыл бұрын
I appreciate that!
@BustaCap4 жыл бұрын
This is exactly what I need! Thank you!
@poom_laupat4 жыл бұрын
Wow. Just wow. I thought u gotta use index(match) there. Really nice trick for the virtual table one. Super cool.
@valentindistefano87314 жыл бұрын
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
@ExcelGoogleSheets4 жыл бұрын
Thanks :) Welcome aboard!
@ciromontes892410 күн бұрын
thank you so much excllent content!
@giancarloschafloqueguzman99183 жыл бұрын
Excelent
@dsky66894 жыл бұрын
Great! New knowledge!
@raphaelobadia2434 жыл бұрын
hi, first thx for your videos. small question : Does B2:B10 vs B2:B has an impact on speed with a very large spreadsheet?
@ExcelGoogleSheets4 жыл бұрын
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.
@lazalazarevic61924 жыл бұрын
Great video!
@kidszone24253 жыл бұрын
I want to apply array and match function to multiple google sheet and vlookup . How can I do that. Please guide
@ConsulthinkProgrammer4 жыл бұрын
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?
@GustavoMartinsShow4 жыл бұрын
Nice tutorial!
@parekhhemantm2 жыл бұрын
Thank you so much.... sir,...
@ExcelGoogleSheets2 жыл бұрын
:)
@aakritimoda44022 жыл бұрын
This content is advanced level. Would be great if you could share the data sheet link so that we could practise along
@brunocesardecastromachadob34974 жыл бұрын
Excelente!
@adicalin77794 жыл бұрын
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.
@ExcelGoogleSheets4 жыл бұрын
I'm not sure. If you share and example sheet with input->output example I might find a solution.
@adicalin77794 жыл бұрын
@@ExcelGoogleSheets Okay. I have an example sheet ready to share. Which email? The one starting with chicago at gmail?
@ExcelGoogleSheets4 жыл бұрын
Please share here in comments. Emails are not read for security reason.
Not sure how large of a dataset it will support, but I have an example in your sheet.
@0x00nobody74 жыл бұрын
Would be good if you posted the other video's you've mentioned - in your video description as well
@ExcelGoogleSheets4 жыл бұрын
kzbin.info/www/bejne/kJCygqVuipyFe5I
@hivinay832 жыл бұрын
Thanks
@ExcelGoogleSheets2 жыл бұрын
👍
@pichit.raetai4 жыл бұрын
Thank you
@ali5t4ir3 жыл бұрын
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?
@ExcelGoogleSheets3 жыл бұрын
Yuo could wrap it in a QUERY function and format your columns, but why not just format the column manually?
@ali5t4ir3 жыл бұрын
@@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)))
@ExcelGoogleSheets3 жыл бұрын
=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.
@ali5t4ir3 жыл бұрын
@@ExcelGoogleSheets you are simply amazing, Im subscribed THANK YOU!
@fttx66733 жыл бұрын
Thank
@lpanebr4 жыл бұрын
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.
@ExcelGoogleSheets4 жыл бұрын
I'm pretty sure I've already covered this, just not sure in which video :)
@melihdiyala93072 жыл бұрын
It’s covered in this video at 8:30 kzbin.info/www/bejne/pJXbgHiYnMqkpbM
@lpanebr4 жыл бұрын
👏👏👏👏👏👏👏
@Jolly00713 жыл бұрын
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
@factsnexts2720 Жыл бұрын
Hello sir I want to this sheet for practice...
@vikasaggarwal131011 ай бұрын
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)
@sungguhsetianto89543 жыл бұрын
Sir, thank you, but why it does not work for me?..i writed all the formula, but only one result that come up..
@DragonXDrei3 жыл бұрын
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.