VLOOKUP Challenge Training Series

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.

VLOOKUP-Challenge-START.xlsx

VLOOKUP-Challenge-Solution.xlsx

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! 

214 comments

1 2 3 … 5

Your email address will not be published. Required fields are marked *

  • Hi Jon,

    Do you have a link to the article on “match” that can replace the helper column that you mentioned in Video 4: Interactive Vlookups with Drop-downs & Conditional Formatting?

    Thanks

    Reply

  • Hi Jon,

    I tried using INDEX/MATCH in an additional column in “Add Delete Columns” sheet of the Solution workbook.

    I keep returning the incorrect email addresses when using the following formula:

    =INDEX($J$4:$J$1003,MATCH(F4,$F$4:$F$1003,0))

    Please advise where I am going wrong?

    Thanks

    Reply

    • Colin, you need to change the MATCH lookup_value. It defines what you are looking up – in the example it should be the name on the left:
      =INDEX($G$4:$G$1003,MATCH(B4,$E$4:$E$1003,0))

      Reply

  • Hi Jon,

    I thought this might be good way to provide error trapping in “Add Delete Columns” sheet.

    =IFERROR(IF(VLOOKUP(B4,$E$4:$G$1003,3,0)=0,”Missing Email”,VLOOKUP(B4,$E$4:$G$1003,3,0)),”Missing Name”)

    You are able to make the error(s) more visible when either the lookup value (Name) or return value (Email) is missing.

    My one question would be if both the lookup value and return value are missing, can you add to this formula to include a third error criteria?

    Thanks

    Reply

  • Hi Jon – Thanks for the bonus training.

    When using the column function, does the column you’re pulling the data from need to always be the last column in the array? For example, if you were trying to look up the phone number in column index 2, what happens when columns are added?

    Reply

  • Jon – One more question, is there a way for vlookup to search in a column other than the leftmost one?

    Reply

  • Hey Jon!
    Great stuff as always. Love the Columns function in the formula to lock down the target if the lookup table is altered.
    On challenge 5, recommend you update the instruction balloon to reference the correct lookup sheet (“Category Table” vice “Lookup Table”), and change the column U reference at the end?

    Keep up the great work!

    Reply

  • For the add delete columns challenge I used (after making the lookup area a table):
    =VLOOKUP(B4,Table1[#All],MATCH($C$3,Table1[#Headers],0),FALSE)

    For the address list I used:
    =IFERROR(VLOOKUP($C$5,$E$9:$J$26,MATCH(B7,$E$8:$J$8,0),FALSE),””)

    The conditional formatting was the one area where I learned something. Thanks Jon!

    Reply

  • Great lessons I learned a lot from your video and I will share or refer this video to all of my friends who love to learn and improve their basic vlook up

    Reply

  • I’ve been using excel for many, many years in the manufacturing industry. Although we use more basic functions, normally, I’ve been watching your videos over the past two days and I can’t believe how much I’ve learned and been missing out on.

    I could have implemented so many of these tools over the years and saved a lot of time and frustration.

    You teaching style is GREAT. Clear and easy to understand and follow.

    Thank you so much.

    ~Orlando

    Reply

  • Hi Jon,
    Thank you so much for your videos it is impossible not to learn anything new or anything I had all forgotten. You are very detailed and dedicated that I hope that God blesses you with the gift to keep on helping so many people.
    Once again thank you so much Jon.

    Reply

  • Hi Jon!!
    Thank you for the great explanation that you show us with your videos and step by step learning process of Excel.
    Very helpful and refreshing.

    Thank you.

    Reply

  • Hi Jon!
    First of all, thank you for the excellent tutorials!

    I tried to solve the drop down list formula with Match function and it doesn’t work.
    When I try to copy it down to the next fields, the first argument of the match function changes downward, to F9, instead of G8. I also tried F$8 in the formula and still nothing!

    =VLOOKUP($C$5,$E$9:$J$26,MATCH(F8,E8:J8,0),FALSE)

    I’d be very happy if you can help
    Thanks

    Reply

    • Here’s what I did.

      =VLOOKUP($C$5,Table12,MATCH(B7,Table12[#Headers],0),FALSE)

      that’s when i turned everything into a table, but before that, i used columns but that wouldn’t work and will require maintenance, so I used match and made it into a table and here’s the formula,

      =VLOOKUP($C$5,$E$9:$J$26,MATCH(B7,$E$8:$J$8,0),FALSE)

      I hope this helps 🙂

      Reply

  • Hello Jon

    Thank very much. this is helping me as I prepare for my interview.
    easy to understand and follow.
    keep up the good work.

    Charlotte.

    Reply

  • Thank you so much. You have such a wonderful way of teaching. I could listen to you all day!! So informative and such a lovely voice to listen to. Such a great resource you have put together for us all. I’m looking forward to more of your training tutorials. This is one of the best training videos I have ever watched 🙂

    Reply

  • Fantastic videos! I am learning so much. I am trying to determine how to find duplicate numbers in column 1 of one table, compared to column 1 in a new data set. I am getting 12 months of data each month and looking for duplicates to determine status changes on my data. For example, my static data has 12345 on a row in column 1 with a status in column 2 of ”approved”. My new data set has 12345 on a row with a new status of closed. My data is over 2000 lines long. I am only trying to find the duplicate values that may have changed between getting a new data set. Is vlookup the best choice?

    Reply

  • Good training. Excellent format. I got lost on Video 4. I’m someone who has never used anything more than @SUM. I’m learning terms, how to get around the site, took me about 3 times and about 30 minutes to figure out to keep going back to the table of contents for the next session. But I figured it out, finally. By then I was already behind because taking in too much info.

    Going back to the into and start again. This time I know more terms and how to get around. Not giving up.

    Reply

  • Thank you, Jon! Incredible course and I love the bonus challenges! I love how you expand and suggest interesting areas to increase robustness and end-user friendliness. If anyone wants excel training I will 100% recommend Excel Campus!

    Reply

  • I learned so much from this session. The downloadable Excel practice workbook, was awesome to practice and follow along with the video. The 10 Tips PDF was great as well.

    Reply

  • Thank you very much for VLOOKUP videos and the bonus videos as well , it was the best videos explaining excel in so smooth way .

    I was looking for a function that could make a search in a table for a certain criteria or condition , e.g. I need the clients who has pained more than 100$ in last month . is that possible ?

    Reply

    • Hi Hany,
      Thank you for the nice feedback! I really appreciate it.

      Great question! There are a few ways to go about this. If you are on Office 365 then the new FILTER function is probably the easiest way. It allows you to return a spill range of multiple cells that meet certain criteria.

      Here is a post and video on the new dynamic array functions, including FILTER.

      https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/

      Another easy option is to use a pivot table and apply a filter for the criteria. This is especially useful if there are multiple rows for each client and you want to sum their payments first.

      We have a intro series on pivot tables and dashboards if you have not used them yet.

      https://www.excelcampus.com/charts/pivot-tables-dashboards-part-1/

      I hope that helps. Thanks again and have a nice weekend! 🙂

      Reply

  • Jon, thank you so much for sharing your Excel knowledge. These tutorials have been a blessing. God bless you.

    Reply

  • Hi Sir,
    I have one doubt. While practicing VLOOKUP challenges, in the order table, the question is to return the product category from the category table. When I am writing the formula in the product category and clicking E4, the name E4 is coming within brackets- =VLOOKUP(E4,…… However, when I am selecting A2 in category table, it is coming as =VLOOKUP(E4, ‘Category Table’!A2….
    My question is why E4 is coming within the brackets?
    Ii would be very helpful if you kindly answer my doubt.
    THANK YOU

    Reply

  • When using structure referencing of excel table, is there still need for locking the cell? shifting to relative referencing. We used absolute referencing without excel table.

    best,
    Abdul

    Reply

  • Good morning Jon,

    Firstly, I’d like to thank you for spending so much of your time putting these tutorials together.

    Secondly, I wanted to tell you how easy and well crafted these are to follow. I consider myself a fairly proficient Excel user but following these tutorials gives me that extra capability. I’ve already utilized Tables and VLOOKUP on another project, and it is working exceptionally well.

    Thirdly, I am finding it difficult to stop working through all of your material. Being in this quarantined state, I have much more free time, however, I’m also using this newfound time to launch a new business. This is where the Tables and VLOOKUP are coming in so handy.

    Anyway, I just wanted to share with you how wonderful I think that you are for providing all of this free material. When I am able, I will certainly consider becoming an Excel Campus member and help pay for some of this material that you have so graciously provided.

    Sincerely yours,
    Bob

    Reply

    • Hi Bob,

      Thank you so much for the nice feedback. I really appreciate your kind words and I’m happy to hear the videos and training material are helping you.

      I’m also happy to hear that you are using your free time to learn and develop your skills. This is awesome! I look forward to having you as a member of our programs someday.

      Wishing you all the best with your new business as well. Congrats!

      Thanks again and have a nice day! 🙂

      Reply