Search

CP002: VTALKUP – 5 Excel lookup functions demystified + 4 Resources for you

Share

In the second session of Chandoo.org Podcast, We will be learning how to use 5 Excel lookup functions.

Good News – We are on iTunes and more

Before we proceed with the show, let me share few very happy news with you all.

• Chandoo.org Podcast is on iTunes. What more, we are featured in New & Noteworthy section too. Click here to subscribe.
• Our show is on Stitcher and Windows Phone Podcasts too: Click here to subscribe on Stitcher. For Windows phone, search your podcasts app for our show.
• As always, you can get latest episodes, show notes & resources from our Podcast page.

What is in this session?

In this session, we tackle one of the most important areas of Excel. The lookup functions.

You will learn,

• Why lookup functions are necessary
• 5 Important lookup functions in Excel – VLOOKUP, HLOOKUP, LOOKUP, MATCH & INDEX
• When & how to use each of these 5 functions?
• Extreme scenarios:
• What happens when the value you are looking up is not there?
• What if too many items match the lookup value?
• What if you have too many conditions in the lookup criteria?
• Using IFERROR function
• Re-cap of the new powers you acquired
• 4 Resources for you to learn lookup functions better

Go ahead and listen to the show.

``` https://traffic.libsyn.com/chandoo/CP002.mp3```

Links & Resources mentioned in this session:

4 Resources to learn lookup functions:

Example workbook for this podcast:

Other formulas and links mentioned in this podcast:

• Listen to this session to unlock a special gift for you.

Thanks to Jordan

I almost forgot. Special thanks to Jordan (@SpikingWhamos) for suggesting VTALKUP as a name.

If you enjoy the podcast…,

Please do these 2 things. It means a lot me & our community.

1. Subscribe to our podcast on iTunes
2. Please leave an honest review on iTunes by clicking here.. It will help the show & its rankings immensely. Thank you so much.

Now its your turn to talk

Do you use lookup formulas in Excel? What do you use them for? Where do you struggle? What new things have you learned about them thru this podcast? Please share your views, suggestions, questions and tips in the comments area below.

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

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.

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.

Extract BOLD portion of a cell in Excel using getBoldText() function

Use the getBoldText() function in Excel to extract bolded portion of a cell automatically. Saves you time and helps with data cleaning.

Related Tips

Chandoo.org Podcast Sessions

CP05: Interview with MrExcel – Bill Jelen (on his incredible work ethic)

Chandoo.org Podcast Sessions

SQL vs. Power Query – The Ultimate Comparison

Chandoo.org Podcast Sessions

CP03: The Ugly Truth About Power BI (actually, 4 of them)

Chandoo.org Podcast Sessions

CP02: Six Tips to get a Six Figure (\$100,000+) Data Job

Chandoo.org Podcast Sessions

Top 5 Excel Skills you need to be a Successful Data Analyst in 2023 (podcast)

Chandoo.org Podcast Sessions

17 Responses to “CP002: VTALKUP – 5 Excel lookup functions demystified + 4 Resources for you”

1. jraju says:

Hi, The Second podcast is awesome. I had little knowledge about this particular function. The 45 minutes lecture definitely throws more light, particularly on limitations of vlookup. The usage of False, even when i used , using excel model help example, i did not understand. Now i understand it thoroughly.
Like wise, usage of hlookup, lookup (sorted data columns),match index lectures are to the point and it is given with practical yellowpage example. Thanks so mcuh.
The usage of ,0 at the last in match, i will understand by going to the resource link. Thanks, i have sent the links, both mp3 and example book to my friends.

• Chandoo says:

Thanks jraju for the love...

• jraju says:

Hi, Thanks
Will the resource links for if error and other formulas will be permanently available for future reference in the podcast page

2. LeonK says:

Hi Chandoo. Thank you for the podcast. I gave up using LOOKUP after discovering your site and only use Index/Match. However, a lot of my internal clients use LOOKUPS and your podcast has really made me look at these formulas again, allowing me to support them in their formulations instead of trying to teach them Index/Match - regardles of my own opinion.

May I please ask you a question. In your sample file, both Jason and Stella received \$15,000 bonuses but only Jason appears in an index/match table (dashboard summary). How would I show the full range of names sharing the same given criteria and in alphabetical (Sorted) order - without VBA.

If you can answer this for me I would be very grateful.

Kind regards,

LeonK

• Chandoo says:

Thanks for the appreciation LeonK.

You can use below approach to show all matches in originally listed order.

1. use this formula for first item:

=INDEX(\$B\$6:\$B\$13,SMALL( IF((\$E\$6:\$E\$13=15000),(ROW(\$E\$6:\$E\$13)-ROW(\$E\$5))),1))

CTRL+Shift+Enter

2. to get 2nd item, replace 1 in the end with 2.

To sort these in alphabetical order, use this technique:

http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/

3. Sanmaya says:

hi Chandoo,
the podcast is very interesting. and examples are really cool.

4. dan l says:

And for the 3 people in the universe who have windows phones......heh

• Chandoo says:

I can't beleive my Indian English accent can be so unintentionally cruel. What I said is "for the people with Windows phone". Not for three.

• Hui... says:

@Dan
Is there 3 ?

5. […] Chandoo has a podcast now, so you can hear him talk about Excel, while you’re riding the bus to work. His latest show is about Lookup functions. […]

6. Bhavesh K says:

Thanks Chandoo!!!

Went thru 2nd podcast of yours. Learnt alot. Had a different perspective with vlookup earlier, which was very limited in terms of its applicability. Concept of Match and Index got clearer then earlier. Confident now, that i will be able to use the same in my job and enhance my productivity.

Keep Sharing!!!
Thanks Again,

7. Bill Wilson says:

Suggestion to convert to video podcast +1

8. Jack says:

I can't find session 3

9. Ranveer Singh says:

Hi Chandoo,

Can you please tell me why do we have =+ when we start any formula??

• Hui... says:

@Ranveer
That maybe copied from me Ranveer

I grew up on a spreadsheet called Open Access
It was a DOS based spreadsheet and way ahead of the rest at the time

Open Access started Formulas with a + sign and so I still do that
Excel is happy to accept that and simply adds an = to the front end

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.