The INDEX-MATCH formula cannot only look up values to the left and right of your data but its versatility means that it can also lookup the largest values within an array.

Say that you had a list of Sales Reps in one array and their corresponding Sales Values in another array.

By nesting a MAX function, you can tell the formula to find the biggest Sales Value and return the Sales Rep corresponding to that.

Here is how….


Index-Match - Max Sales


Power Excel

If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Advanced Sumproduct Function: Sum the Top 3 Sales ... This is probably the most advanced level a SUMPRODUCT function can reach and that is by including a nested array formula.In our example below we want to return the 3 Largest values from the North region and sum them up.  As we are asking our formula to perform multiple calcul...
VLOOKUP Multiple Columns What does it do?Searches for a value in the first column of a table array and returns the sum of values in the same row from other columns (to the right) in the table array.Formula breakdown:{=SUM(VLOOKUP(lookup_value, table_array, {col_index_num1,col_index_num2}, ))}...
Getting the length of text with Excel’s LEN ... What does it do?Gives you the number of characters of the textFormula breakdown:=LEN(text)What it means:=LEN(text that you want to get the number of characters)There are times when you need to get the number of characters within a cell in Excel.  Thank...
Sum a Range Using the INDEX Function You can sum a range of values within a table using the INDEX function in Excel.  This is valuable when you want to extract key metrics from a table and put them in an Excel Dashboard.To make this work you firstly need to start your Excel formula with the SUM function foll...