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.

### 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

### Resources for this podcast

4 Resources to learn lookup functions:

Other formulas and links mentioned in this podcast:

Speeding up VLOOKUPs:

### Transcript of this session:

 How can I help you in 2016? [Survey] Don’t make your charts heavier than they should be – The weight of the world chart [case study]
 Written by Chandoo Tags: excel optimization, INDEX(), MATCH(), Microsoft Excel Formulas, podcasts, vlookup Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

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

 How can I help you in 2016? [Survey] Don’t make your charts heavier than they should be – The weight of the world chart [case study]