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:
|