Look Who it Was Random video

Excel - Excel Tutorial: Find the Largest Sales with INDEX and MATCH Functions - Episode 747

593 views · Published 5 February 2009 · 4:10 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Who is the largest sales?

Welcome back to the MrExcel netcast! In this episode, we'll be discussing a useful Excel formula for finding the largest and smallest values in a dataset. But first, I want to remind you about our upcoming spring seminars. If you're in the Princeton, New Jersey or South Bend, Indiana area, join us for our half-day Power Excel seminar on May 6th and 9th. We'll also be in St. Louis and Springfield, Missouri on May 13th and 14th, and in Dallas for a 3-day seminar with Mike Alexander on May 21st-23rd. And for those feeling adventurous, we'll be in Trinidad on June 2nd. Check out our website for more details and to register.

Now, let's dive into today's topic, which was inspired by a question from Lucy. She was struggling to find a way to show which sales rep had the largest or smallest sales in her spreadsheet. After some trial and error, we came up with a solution that involves using the MIN and MAX formulas in hidden columns, and then using the MATCH and INDEX functions to display the corresponding sales rep's name. This may seem complicated, but it's actually a very flexible and efficient method for finding the largest and smallest values in a dataset.

To use this method, simply create two new columns next to your dataset and use the MIN and MAX formulas to find the smallest and largest values for each sales rep. Then, use the MATCH function to locate the row number of the desired value, and wrap the INDEX function around it to display the corresponding sales rep's name. Just remember to hide these columns so they don't interfere with your data. And if you need to update your dataset, the formulas will automatically adjust to reflect the new values.

I hope this tip helps you in your Excel endeavors. Don't forget to check out our website for more helpful tips and upcoming seminars. Thanks for watching and we'll see you next time for another netcast from MrExcel!

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 to upcoming seminars
(00:26) Upcoming seminars in various locations
(00:41) 3-day seminar in Dallas with Mike Alexander
(01:04) Details on upcoming seminars on MrExcel website
(01:14) Question from Lucy about using MIN and MAX formulas
(02:03) Using MATCH and INDEX functions to find largest and smallest values
(03:18) Example of using RANDBETWEEN function
(03:37) Reminder to hide columns for MIN and MAX formulas
(03:51) Conclusion and invitation to next netcast

#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:
Excel formulas
INDEX formula
MATCH formula
MAX formula
MIN formula
Power Excel seminar
Sales analysis
Sales reps
Spreadsheet tips
Spring seminars
Travel

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



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.

More from this channel