MAX, MATCH, VLOOKUP AND LARGE functions in data analysis
We have the salray data of the employees in a company and wish to find the highest salary, the name of the person drawing the highest salary and also wish to know who draws the third largest salary. There are many ways to accomplish this in Excel but let’s see how we can achieve our goal using simple formulas involving ‘MAX’, ‘MATCH’ ‘VLOOKUP’ an the ‘LARGE’ functions.
- We use the MAX function with the named range ‘salary’ to find out the highest salary.
- The MATCH function helps us to find the row number that contains the employee with the largest salary.
- The VLOOKUP function then finds the employees name.
- The ‘LARGE’ function is then used to find out the 3rd, 4th or 7th largest salary from the ‘salary’ range.
- You can confirm the value returned by the large function by comparing it with the value returned by the RANK function
Before using the above functions we name the range A1::C11 as ‘mydata’ and the range C3:C11 as ‘salary’. Now we use the above functions appropriately as shown in the Excel training video below:
Top 10 Formulas for Aspiring Analysts