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.

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!



  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