Menu
CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered
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
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.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « 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] » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
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