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.
Podcast: Play in new window | Download Subscribe: RSS
Links & Resources mentioned in this session:
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
Example workbook for this podcast:
Click here to download example workbook to play with the ideas mentioned in this podcast.
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
Special gift for you:
- Listen to this session to unlock a special gift for you.
Transcript of this session:
Download session transcript [PDF]
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.
- Subscribe to our podcast on iTunes
- 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.
17 Responses
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.
Thanks jraju for the love…
Hi, Thanks
Will the resource links for if error and other formulas will be permanently available for future reference in the podcast page
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
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/
hi Chandoo,
the podcast is very interesting. and examples are really cool.
And for the 3 people in the universe who have windows phones……heh
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.
@Dan
Is there 3 ?
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,
Suggestion to convert to video podcast +1
+3
Hi Bill and rest, we do post regular videos on our YouTube channel. The audio podcast is to cater to different needs.
For video, visit http://www.youtube.com/user/ExcelTutorials
I can’t find session 3
Hi Chandoo,
Can you please tell me why do we have =+ when we start any formula??
@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