Look Who it Was Random video

Excel - VLOOKUP from Multiple Tables with Different Commission Rates | Dueling Excel - Episode 1165

1,354 views · Published 24 December 2009 · 9:31 · Indexed 5 October 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: VLOOKUP from Multiple Tables with Different Commission Rates | Dueling Excel.

Welcome to another episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel and I'm joined by Mike Girvin from ExcelIsFun. In this episode, we're tackling a great question from one of our viewers about using the TRUE version of VLOOKUP to look up data from three different tables. But the twist is, the tables have different commission rates depending on the product being sold. So how do we handle this in Excel? Let's find out!

First, we'll start with the traditional approach using VLOOKUP and the INDIRECT function. We'll use the CHOOSE function to select the correct table based on the product being sold, and then use INDIRECT to create the sheet reference. The only downside to this method is that INDIRECT is a volatile function, meaning it recalculates every time a change is made. But don't worry, we have a solution for that too.

Mike then introduces a brilliant formula that uses the CHOOSE function to create sheet references without the use of INDIRECT. This formula is not only efficient, but it also eliminates the need for volatile functions. But wait, there's more! Mike takes it a step further and shows us how to use named ranges in the CHOOSE function, making the formula even more elegant and efficient.

But the learning doesn't stop there. As we dive deeper into the CHOOSE function, we discover that it can also use defined names as arguments. This is an obscure but powerful feature that Mike has uncovered through his extensive knowledge of Excel functions. We also take a moment to appreciate the helpful function arguments that Excel provides, thanks to Mike's curiosity and dedication to learning.

We hope you enjoyed this episode of the Dueling Excel podcast and learned some new tips and tricks along the way. Don't forget to tune in next week for another exciting episode from MrExcel and ExcelIsFun. And from all of us here, we wish you a safe and happy holiday season. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction by Bill and Mike
(00:25) Discussion of the problem and solution using VLOOKUP and INDIRECT
(01:10) Explanation of the commission tables and their different rates
(02:08) Alternative solution using CHOOSE and INDIRECT
(03:09) Mike's solution using CHOOSE and named ranges
(05:35) Bill's improvement using named ranges in CHOOSE
(08:14) Discussion of additional tips and tricks learned during the podcast
(09:07) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
CHOOSE function
Commission rates
Commission tables
Copying sheets in Excel
Excel functions and arguments
Excel tips
INDIRECT function
Paste names in formulas
Sales amount lookup
Using defined names in CHOOSE function
Using range names in formulas
VLOOKUP with TRUE


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152779/

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.

More from this channel