CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered

Posted on January 7th, 2016 in Chandoo.org Podcast Sessions - 3 comments

In the 51st session of Chandoo.org podcast, let’s discuss most frequently asked questions about VLOOKUP.

VLOOKUP FAQs - Frequently asked questions about Excel VLOOKUP formula

What is in this session?

In this podcast,

  • What is VLOOKUP?
  • What happens when VLOOKUP can’t find the value?
  • Should my list be sorted?
  • Is VLOOKUP slower than INDEX + MATCH?
  • What if my list has multiple matches?
  • How to fetch 2nd / 3rd matching item?
  • How to fetch all matching items?
  • How to fetch items matching multiple conditions?
  • How to speed up VLOOKUP?
  • Why doesn’t my VLOOKUP work?
  • What to do in case of errors?
  • Resources for you

Listen to this session

Click here to download the MP3 file.

Resources for this podcast

4 Resources to learn lookup functions:

  1. Comprehensive guide to Excel lookup functions
  2. VLOOKUP Cheatsheet
  3. The VLOOKUP Quiz – test your knowledge
  4. Recommended book: The VLOOKUP Book

Other formulas and links mentioned in this podcast:

Speeding up VLOOKUPs:

Transcript of this session:

Download this podcast transcript [PDF]

Want to know more about VLOOKUP? Please ask…

Still have questions about VLOOKUP? Please post them in the comments section. I will try to help you.


Do you want to be awesome in Excel?

Free Excel tips book - joining bonus - Chandoo.org newsletter

Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

3 Responses to “CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered”

  1. Rob T says:

    Hi Chandoo. Happy New Year!

    On the topic of "Why doesn't my VLOOKUP work?", where you are talking about invisible characters in text strings, there is another cause which I find quite common:

    When the value you're using to lookup is a numerical ID "number", it will often be formatted as text in an imported list (correctly so, since doing maths with IDs would be meaningless). But any manually-entered IDs will normally be designated by Excel as a number.

    They may look the same on screen (sometimes with a give-away error checking triangle symbol on the text-formatted cells), but VLOOKUP and MATCH won't work.

    In such cases, the Text To Columns feature is your friend, as it can change a whole column of numbers into text in one go. However, there's always the danger that IDs starting with zero will have had these stripped while formatted as a number!

    Cheers!

    Rob

  2. Mark says:

    Thanks for this post Chandoo. The deficiency of VLOOKUP() - it can't do a fast lookup in a sorted, sparse matrix - bugged me. I didn't think of doing two VLOOKUP()s to optimize the calculation time until I clicked your Faster VLOOKUPs with Sorting link (above), that took me to Bill Jelen's article "VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP".

    My data is approx 20,000 rows - sorted but sparse (not every key value is in the table). Using Bill Jelen's tip reduced the calculation time on my pc from about 4 minutes to under 15 seconds.

    I wish you continued success in 2016.

  3. shirish b tiwari says:

    i have one excel file filled with details of product sheet which contain some calculation and decimals output. but when i use mail merge this sheet with microsoft word file mailing is done properly but few excel data give me some infinite decimals how to solve it i tried with round function in excel but i need upto two decimals of data in ms word . if u can help

Leave a Reply