Thanks again for taking on the challenge!
This page contains the free bonus challenge file and video series from the Introduction to VLOOKUP video.
Please leave a comment at the bottom of the page with any questions.
The Challenge
In the following video I explain the challenge. You can download the files below. Use the START file to take on the challenge. The SOLUTION video contains all the solutions that I explain in the videos below.
Double-click video to view in Full Screen HD.
Video 1: Writing a Basic VLOOKUP Formula
Video 2: VLOOKUP to Other Sheets & Handling Errors with IFERROR
Video 3: Adding & Deleting Columns
Alternate Solution
There are many ways to make the column index number dynamic. Here is article that explains how to find the column index number by searching for the name of the column with the MATCH function.
VLOOKUP & MATCH – A Dynamic Duo
The MATCH fucntion can also be combined with the INDEX function to achieve the same result.
Video 4: Interactive Vlookups with Drop-downs & Conditional Formatting
Video 5: Excel Tables and Structured Reference Formulas
Conclusion
I hope this series helped you learn some new Excel techniques and skills. VLOOKUP is an extremely powerful function with a lot of different uses. These videos just scratch the surface of what is possible, but should give you a good starting point.
Please leave a comment below with questions or suggestions for additional topics you want to learn.
Thank you!
hi, how can i display video number 4. because i unable to click it
I have looked for so many Excel tutorials, and I have to say that your classes are great, easy to understand and to follow, Great Job, and thank you
Hi Jon,
Thanks for your great explanations, analogies, visuals, and practice worksheets. You do well at making the concepts intuitive and easier to recall and implement.
At the moment I am in video 4 above and having a problem. Around 0:43 you describe how after highlighting the table array it automatically comes back as absolute values because the prior value had. When I do the same though, the table array returns as a relative value and I have to manually convert it to absolute. Any idea why? I am using Office 365.
Thank you.
Thank you for this tutorial, it helps alot
very useful challenge
You’re a legend John, thanks for educating me on so much to do with Excel!
Hi, I have learnt a lot in this video and i’m looking forward for more tutorials with you.
Thank you.
Great thank you this is amazing help
Thank you, Jon, for how well you prepare and present the materials used in your videos. You are a gifted instructor.
Totally awesome! Can you use the columns function in a table?
Thank you for this tutorial, I enjoy it
I’ll try this later after I finished all my work. Thank you very much!
Hi,
VLOOKUP demos are Excellent. Simple and easy to understand with the examples. I learned new technics and really enjoyed. Thanku so much.
I really appreciate these videos. Your patient explanations are so very helpful.
If the video can help me find the job, I would like to pay the course to you.
Great effort and job, Many thnaks, it’s realy helpfull videos.
GREAT tutorials, so clear and easy to follow! congrats!
Hi Jon, I really like your tutorials! They are so easy to follow and I appreciate that you also give us the files. It makes our job easier and we have more time for practice :). Thank you for sharing all this with us!!!
Excellent, Many thanks.
Great video to learn excel for free. Thanks for sharing the video. It has certainly help to improve my excel skills. Great job Jon.
it’s so good I love your tutorial!! Thank you!!
Power query file is not opening after downloading. Its showing unsupported format and am using 2019 Office version. So, please help me to get rid of it.
I cannot properly download the challenge zip files. I only get 1 KB of data.
Thank you the Challenge Training helped me a lot.
I enjoyed the challenge. ♥♥♥
i’m begginer sir. i’m thankful to you. Since i started to watch your video i’m enhancing my knowledge begginer to advance level.
Thank you so much sir
Jon, where have you been my entire Excel-oriented life? 😉 These tutorials are amazing. Thank you so much!!
Best Wishes,
very useful
You have indeed made Excel more fun and easy to use 🙂
These tutorials have helped me a lot, the way you explain things are very clear, easy to remember and logical. Thank you!
The ABSOLUTE BEST Excel tutorials I have ever watched – and I’ve watched hundreds!! The exercises and challenges really make you think for yourself. The author, Jon, explains everything so explicitly. Thank you SO much for creating these videos. They are the only ones I need to refer to going forward. 🙂
Fabulous training content! Thanks Jon
Great job, thanks for sharing!!
Hi Jon all the way from Papua New Guinea. I’ve recently moved into a product role which requires extensive work using excel. I can say confidently that me coming across your video tutorials was not by chance. It’s like the stars have aligned for me!
Really awesome work done with the easy to follow videos, exercises and additional links to other excel functions. Thank you so so much again for the excellent tutorial put together. I will definitely share with my colleagues who wish to improve their knowledge of excel.
Oh and can’t wait for the excel blueprint, live webinar on May 11th.
You have a fan here my friend.
Cheers and regards,
John
Hello Jon,
This is an excellent training material.
We need more material for practice.
Best Regards
James
Thank you so much for this challenge , Your explanation are so easy to understand and I am happy I came across your channel on YouTube.
I’m currently taking a data analysis course, and wow, your teachings in Excel is top notch. I learn better watching your course videos than listening to my data analysis instructors. Great job!
Hi Jon,
A new subscriber here (subscribed last week) and this is my very first communication to you as I started to watch your videos.
I have downloaded the challenge file for the VLookUp session you did and followed it, however I have question regarding one of the challenge, see Tab –> “Add Delette Column” where the formula needs to be changed in order to always be true should a column be added/deleted to the LookUp table.
I’ve done it following your example of course and it is just as expected – see:
=VLOOKUP(B4,$E$4:$G$1003,COLUMNS($E$3:$G$3),FALSE)
But then I went the next step, and Added a columns (to try it out) and called it “address”.
Then I realise that the formula only return a value for the ’email addresses’ on the LookUp table, but what if I now wanted to change that to the one of the new columns instead (like the ‘address’ column in my case) –
There isn’t a column number to add to the formula anymore, so I’m only getting value for the email address, no matter how many columns I add.
So is there a formula that could return:
* not only the email address
* but that could also be modify to return data from those new columns
* with the options of keeping the ‘COLUMNS’ part in the formula in case the LookUp table keeps on expanding?
If yes – do you have a video on that?
Not sure whether my question is clear enough,..
Now that I’ve added a column to the table, it’s quite a bother that no matter the number of columns added, it is only beneficial for returning value for the email address, and nothing else….which does not seem to be a very beneficial formula in the end…
thank you so much!
very helpful!
You are the best at explaining things in simple language. Great tutorials. Thank you
Amazing. I really learnt a lot. Love It.
I have looked for so many Excel tutorials, and I have to say that your classes are great, easy to understand and to follow, Great Job, and thank you
I love the tutorials. It gives great examples.
Regarding “Video 3: Adding & Deleting Columns,” the solution by replacing the column number with COLUMNS function only works when the column number is 3 or 4, in this case, or the last column of the referenced table. If the column number in the VLOOKUP formula is 2 for example, your solution wouldn’t work, since the use of COLUMNS function is intended to reference to the last column.
Would you give us the “ultimate” solution for “adding & deleting columns” to prevent errors when using VLOOKUP formula?
Hi Jon,
Why the formula not matching with you even getting the same result.
Your formula showing as =VLOOKUP(B5,$E$4:$G$1003,COLUMNS($E$3:$G$3),FALSE).
My formula showing as =VLOOKUP(B4,$E:$H,COLUMNS($E$3:$H$3),FALSE).
Your one showing something $4:$G$1003 but my one showing something different.
Sincerely
Nasir Ahmed
thank you, very helpful
Great help. Thank you very much
I was looking last minute crash course for my exam and the lectures are really helpful to understand in most simple manner. Thanks for such a wonderful teaching methodology.
Very to understand great teacher. Highly recommended.
Tony
Thank you for your in-depth tutorial. It was easy to follow and I got to learn the details.
i Jon,
Thanks for your great explanations, analogies, visuals, and practice worksheets. You do well at making the concepts intuitive and easier to recall and implement.
At the moment I am in video 4 above and having a problem. Around 0:43 you describe how after highlighting the table array it automatically comes back as absolute values because the prior value had. When I do the same though, the table array returns as a relative value and I have to manually convert it to absolute. Any idea why? I am using Office 365.
Thank you.
Hi Jon.
Thanks a lot for this exciting challenge. You are truly incredible at the way you break things down till they’re easily digestible.