Learn Excel from Bill Jelen with this daily 2 minute video podcast.
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.
posted by Bill at 3:00 AM
|
0 comments
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.
posted by Bill at 3:00 AM
|
0 comments
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.
posted by Bill at 9:00 AM
|
0 comments
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
Mike & Bill take a look at various ways to do a 2-way lookup in this dueling Excel Episode 1124.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: VLOOKUP
posted by Bill at 9:00 AM
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.
Labels: VLOOKUP
posted by Bill at 9:00 AM
VLOOKUP is my favorite function in Excel. In Episode 1120, we will take a look at the basics of VLOOKUP.
posted by Bill at 9:00 AM
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.
Labels: DATE, dates, Formatting, Text, VLOOKUP
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
Shaun asks how he can link values in one worksheet to a lookup table in an external workbook. Episode 1082 will show you how.
posted by Bill at 9:00 AM
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.
Labels: array formula, MATCH, VLOOKUP
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: custom format, VLOOKUP
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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!
Labels: VLOOKUP
posted by Bill at 9:00 AM
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.
Labels: VLOOKUP
posted by Bill at 9:00 AM
Michael asks how to calculate a sliding commission rate. Episode 757 shows two approaches, both of which use the sorted version of VLOOKUP.
Labels: commission, formulas, VLOOKUP
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: RAND, RandBetween, RANK, VLOOKUP
posted by Bill at 9:00 AM
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.
Labels: VLOOKUP
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
