Excel - Excel Duel: Multiply Numbers with VLOOKUP vs SUMPRODUCT - Episode 1161

2,287 views · Published 18 December 2009 · 6:24 · Indexed 10 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Excel Duel: Multiply Numbers with VLOOKUP vs SUMPRODUCT.

Welcome to another episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel.com and I'm excited to share a cool Excel tip with you today. Joining me is Mike Girvin from ExcelIsFun on YouTube, and we have a great duel lined up for you.

This week, Mike sent me a question from one of his viewers who wants to do a lookup for a specific product and then multiply all the numbers associated with that product. I can only think of one way to do this using the SUMPRODUCT function, but Mike has a different approach that he believes will be a great duel. Let's see what he has in store for us!

Mike's solution involves using the VLOOKUP function and an array formula. He starts by highlighting all the cells that should contain the formula and then uses VLOOKUP to look up the product and return the corresponding numbers. But here's the cool part - he uses an array of values for the return column, which allows him to get all the numbers in one go. He then uses SUMPRODUCT to multiply all the numbers and get the desired result. This is definitely a point for Mike!

But wait, there's more! Mike also shows us how to handle blank cells in the lookup table using the IF function. This is a great trick to ensure that the formula only returns results for the products that have data in the table. Overall, Mike's solution is efficient and creative, earning him another point in this duel.

I also want to thank abstraktus from YouTube for sending in this question. It's always great to see real-world problems being solved using Excel. And of course, a big thank you to all our viewers for tuning in to another episode of the Dueling Excel podcast. We'll be back next week with more tips and tricks from ExcelIsFun and MrExcel. Don't forget to like, comment, and subscribe for more Excel content. 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) Explanation of the problem
(01:00) Bill's solution using SUMPRODUCT and OFFSET functions
(02:22) Mike's solution using VLOOKUP and SUMPRODUCT functions
(05:55) Conclusion and points awarded to Mike
(06:05) 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:
Array formula in Excel
Edit mode in Excel
Excel tips
ExcelIsFun
IF function in Excel
Lookup function in Excel
OFFSET function in Excel
Point mode in Excel
SUMPRODUCT function
SUMPRODUCT with arrays of different dimensions
VLOOKUP function in Excel


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

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

More from this channel