Imagine a scenario wherein we need to find one person on a sales team who sold the most? And we also need to find out how much ahead of the others he or she is?
We will use LARGE() function to determine the highest sales. Then use INDEX() & MATCH() functions to retrieve the name.
Consider below data & our task is to find out the Sales person who has sold the most & how much ahead he/she is from the second best sales person:
Understanding formulas which we have used:
- Large (): This function examines a list of values and picks the value at a user specified position. Syntax: =LARGE(ListOfNumbersToExamine,PositionToPickFrom)
- Index():This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. Syntax: =INDEX(RangeToLookIn,Coordinate)
- Match():This function looks for an item in a list and shows its position. It can look for an exact match or an approximate match. Syntax: ‘=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)
Learn More: Excel Index & Match Function