fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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