Learn Excel from Bill Jelen with this daily 2 minute video podcast.
A viewer from Portugal sends in a question about finding which row contains the match. Episode 1139 shows you a few ways to go!
posted by Bill at 9:00 AM
Today's Dueling Excel Podcast is a question sent in by Hassan. Hassan wants to enter a name and a through-month and then add up that person's sales from January through the selected month. Mike and Bill show various methods in Episode 1134.
posted by Bill at 9:00 AM
Don't use HLOOKUP...just turn the table back to a vertical table using the two methods shown in Episode 1125.
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
Mike and Bill offer different ways of generating a random date between two dates. Episode 1095 shows you how.
Labels: DATE, dates, INDEX, RandBetween
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
Rod from Australia sends in an excellent tip for today's episode. Rod receives a CSV file every day that needs to be transposed. Rod put together an elegant combination of INDEX, COLUMN, and ROW to solve this problem. Episode 945 shows you how.
Labels: INDEX
posted by Bill at 9:00 AM
Can the VLOOKUP return a value one row below the matched value? Episode 854 shows you how to achieve this result using two other functions.
posted by Bill at 9:00 AM
Lucy sends in a question. She has built a table of sales by rep by day. She used the MAX and MIN function to find the largest and smallest value. However, her boss then wants her to identify who had the largest and smallest value. This requires the use of INDEX, MATCH, and a couple of hidden columns. Episode 747 will show you how.
posted by Bill at 9:00 AM
Jon has a follow-up question about his baseball spreadsheet. After calculating the win and loss streaks, he would like to find the longest streak, including the start and end dates. This seemingly simple question requires two completely useless functions. Once these functions are used in conjunction, they become very powerful. Episode 736 shows you how.
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
Richard sends in todays question: How do you copy a formula horizontally when it’s reading from a vertical list? Episode 518 shows the easy, but unintuitive solution.
posted by Bill at 9:00 AM
As we hit this milestone, you can win one of a treasure trove of prizes, including a video iPod, great books from QUE, and more. There are over 40 prizes in all. Also in Episode 500, I draw the winner for the March/April challenge of the month, using a combination of Advanced Filter, INDEX, and RANDBETWEEN. I will use a similar method to award the prizes from our podcast #500 giveaway.
Labels: Advanced Filter, INDEX, RandBetween
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
These two functions allow you to dynamically point to a cell or a range. While they are often useless on their own, they can be used as arguments to another function. Episode 485 introduces these two functions.
posted by Bill at 9:00 AM
