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
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
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
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
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
Robert asks how he can combine an HLOOKUP and a VLOOKUP together into a single formula. This is a common problem, but you don't use either VLOOKUP or HLOOKUP. Instead, Episode 884 will introduce a function called MATCH. This obscure but versatile function is similar to both VLOOKUP and HLOOKUP. Episode 884 shows you how to solve this problem.
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
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
