Learn Excel from Bill Jelen with this daily 2 minute video podcast.

Thursday, December 31, 2009

Ahmed asks how to do a lookup that only looks at the text portion of the lookup value. Mike and Bill compare methods in this Dueling Excel Episode 1169.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Thursday, December 24, 2009

Lookup 3 Tables - 1165 - Dueling Excel Podcast

In today's dueling podcast, we need to look up a value in one of three different tables depending on the product selected. Mike and Bill show many ways to solve the problem in Episode 1165.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, December 18, 2009

VLOOKUP All Columns - 1161

Today's dueling podcast question is how to return the sum of all columns from a VLOOKUP. Mike and Bill show you various methods in episode 1161.

Labels: ,

Friday, November 13, 2009

Duel: Unsorted VLOOKUP - 1144 - Learn Excel from MrExcel Podcast

Martin from Sweden sends in a question about doing a range VLOOKUP where the lookup table is not sorted. Mike and Bill duel it out in today's Episode 1144.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Monday, November 02, 2009

Embedding Lookup - 1135 - Learn Excel from MrExcel Podcast

Nick asks how to do a pretty lengthy bonus calculation. It might be too complex for the Excel 2003 nested IF limit, so I decided to use the range version of VLOOKUP. But...as a twist, I embed the table right in the formula. Episode 1135 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, October 16, 2009

Duel: 2-Way Lookup - 1124 - Learn Excel from MrExcel Podcast

Mike & Bill take a look at various ways to do a 2-way lookup in this dueling Excel Episode 1124.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Thursday, October 15, 2009

Many VLOOKUP Columns - 1123 - Learn Excel from MrExcel Podcast

VLOOKUP week continues.... Today, you have to lookup an account number and return the results from four columns in the lookup table. Episode 1123 shows you four possible solutions.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Wednesday, October 14, 2009

ALL #N/A's - 1122 - Learn Excel from MrExcel Podcast

What happens when you enter the perfect VLOOKUP formula and everything returns #N/A? Episode 1122 shows you some of the sneaky reasons why VLOOKUPs fail and what to do about it.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Tuesday, October 13, 2009

VLOOKUP #N/A - 1121 - Learn Excel from MrExcel Podcast

The most common error from your VLOOKUP formula is the #N/A error. Episode 1121 looks at how to find the #N/A and then three ways of adding those new values to your lookup table.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, October 12, 2009

VLOOKUP 101 - 1120 - Learn Excel from MrExcel Podcast

VLOOKUP is my favorite function in Excel. In Episode 1120, we will take a look at the basics of VLOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Monday, October 05, 2009

Opening Balance VLOOKUP - 1115 - Learn Excel from MrExcel Podcast

Brett from Australia asks how to format a date to include the words Opening Balance so that he can do a VLOOKUP into a report. The format is easy, the VLOOKUP is tougher. Episode 1115 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Friday, September 25, 2009

Duel: Matching Prospects - 1109 - Learn Excel from MrExcel Podcast

BackIn2Shape at YouTube has two worksheets; one of customers, and one of prospects. If a prospect is on the customer list, he would like to delete it. Episode 1109 show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Tuesday, August 18, 2009

VLOOKUP External Workbook - 1082 - Learn Excel from MrExcel Podcast

Shaun asks how he can link values in one worksheet to a lookup table in an external workbook. Episode 1082 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Wednesday, August 05, 2009

Find Last Match - 1073 - Learn Excel from MrExcel Podcast

Sarah from the cattle farm in the UK writes in again with an insanely difficult question. Do a VLOOKUP, but find the last previous entry that matches the vehicle. Episode 1073 shows you a formula from Excel Gurus Gone Wild to solve this problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, July 17, 2009

Dueling: VLOOKUP Left - 1060 - Learn Excel from MrExcel Podcast

In today's dueling Excel podcast, how to do a VLOOKUP left. In Episode 1060 Bill and Mike show differing methods, from INDEX and MATCH to LOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Thursday, February 12, 2009

MrExcel's Learn Excel #949 - Grouping Ages

Steve asks how to group records into age ranges. In Episode 949, I show how to use the range version of VLOOKUP to solve this problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Monday, January 12, 2009

MrExcel's Learn Excel #926 - Double Lookup

Jim has two lists that he wants to match, but some records are missing from one list or the other. There are many ways to solve this. Episode 926 will show the classic double-lookup method.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, November 14, 2008

Episode 890 - Offset as TableArray

An alternative to Episode 889...If you need to match two sorted columns with a VLOOKUP, you can make the table array be dynamically calculated with the OFFSET function. Episode 890 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Thursday, November 13, 2008

Episode 889 - Concatenated Key

You need to do a VLOOKUP that will look up two values from a table. In Episode 889, I will show a method using a concatenated key field to enable VLOOKUP to work.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, October 21, 2008

Episode 872 - VLOOKUP Not Working

Janet wonders why her VLOOKUPs are not working in certain cases. If your key field contains a mix of numbers and text that looks like numbers, then the VLOOKUP won't work. Episode 872 shows how to use the TEXT function to intermittently solve the problem and another method to reliably solve the problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, September 24, 2008

Episode 853 - Preventing #N/A

You love VLOOKUPs, but you hate the #N/A error that results. In our Where is it Wednesday edition of the MrExcel podcast, I will show you two ways to prevent #N/A as the result of your VLOOKUP formulas. One harder method in Excel 2003 and an easier method in Excel 2007. Episode 853 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, June 19, 2008

Episode 784 - Wildcard VLOOKUP

Tom asks how to do a VLOOKUP with a wildcard. This interesting question has many possible approaches to solve the problem and it will become the June/July challenge of the month. I encourage you to enter our contest to win cool prizes. Watch Episode 784 to see how!

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, June 16, 2008

Episode 781 - Unsorted VLOOKUP

I keep hearing a common misconception that the lookup table in the VLOOKUP has to be sorted. In many cases, this is not true. Episode 781 looks at the optional fourth parameter of VLOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Tuesday, May 13, 2008

Episode 757 - Sliding Commission

Michael asks how to calculate a sliding commission rate. Episode 757 shows two approaches, both of which use the sorted version of VLOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Tuesday, February 19, 2008

Episode 697 - VLOOKUP or INDEX?

Mel sends in today's question. She prefers VLOOKUP, but her husband prefers INDEX and MATCH instead of VLOOKUP. In Episode 697, we'll take a look at when to switch from VLOOKUP to INDEX and MATCH.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Monday, August 13, 2007

Episode 570 - Random Quiz

Giovanni asks how he can create a random multiple choice question; present a definition and four answers, one of which is right and three are wrong. While this sounds simple, the solution requires a mix of VLOOKUP, RANK, RAND, RANDBETWEEN functions. Episode 570 walks through the solution.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

Friday, May 11, 2007

Episode 506 - Excel Gradebook

Christina asks how to create a gradebook in Excel. Episode 506 shows off how to create absolute references and also the obscure TRUE version of the VLOOKUP function. Everyone – teachers and non-teachers should check out the VLOOKUP in this episode.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Monday, April 16, 2007

Episode 487 - VLOOKUP LEFT

To finish up the recent discussion of VLOOKUP, INDEX, MATCH, and OFFSET, today’s episode talks about the strange situation where the data that you want is located to the left of the key field in the lookup table. Episode 487 will show you how to use VLOOKUP and MATCH to solve this problem.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Friday, April 13, 2007

Episode 486 - Match & Index

In today’s podcast, we have hundreds of rows of VLOOKUP functions. Learn some tricks to make the entry of the third argument easier using a hidden row or the COLUMN function. Ultimately, though, after several iterations of VLOOKUP solutions, Episode 486 finally gets to the point of replacing VLOOKUP with MATCH and INDEX functions.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

Wednesday, April 11, 2007

Episode 484 - VLOOKUP vs MATCH

The VLOOKUP function is one of the most versatile functions in Excel – you can use VLOOKUP to add item descriptions to a database containing item numbers. Episode 484 shows you how VLOOKUP works. This is the first in a series of podcasts to compare VLOOKUP with INDEX and MATCH.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,