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
Listen to this session
Podcast: Play in new window | Download
Subscribe: RSS
Click here to download the MP3 file.
Resources for this podcast
4 Resources to learn lookup functions:
- Comprehensive guide to Excel lookup functions
- VLOOKUP Cheatsheet
- The VLOOKUP Quiz – test your knowledge
- Recommended book: The VLOOKUP Book
Other formulas and links mentioned in this podcast:
- INDEX formula – introduction, how to use it and why you should use it?
- INDEX + MATCH formula combination
- IFERROR Formula What is it, syntax, examples and uses
- Looking up 2nd or 3rd matching value
- Looking up based on multiple conditions
- Ultimate VLOOKUP trick – Multi-condition lookup
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.
3 Responses to “CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered”
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
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.
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