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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920