Find min&max salary by using match and index

[vamshivk]

C_Code	FirstName	LastName	Birthdate	Gender	M_Status	Department	Region	Basic Salary
150834	Ram	Ambradkar	01-Jun-85	Female	Married	FLM	North	48000​
150784	Sachin	Bangera	28-Aug-77	Female	Single	Digital Marketing	North	35000​
150791	Rajesh	Bohra	01-Dec-63	Female	Married	Digital Marketing	North	67000​		Figure out who has the Max and Min Salary
150940	Rajeesh	C	30-Aug-73	Male	Single	Inside Sales	South	87000​
150777	Melwyn	Crasto	30-Oct-57	Male	Married	Marketing	North	22000​		Name of Employees, who have
150805	Rajesh	Dedhia	27-Aug-71	Male	Married	Director	North	91000​		Max Salary
150990	Dattatray	Desai	28-Aug-99	Male	Married	Learning & Development	Mid West	77000​		Min Salary
150989	Vishnu	Desai	28-Aug-90	Male	Married	Digital Marketing	Mid West	45000​
150881	Dinesh	Dhanuka	21-Jan-83	Male	Single	Digital Marketing	East	92000​
150814	Heena	Dongre	09-Nov-71	Male	Married	Inside Sales	North	50000​
150937	Dhiren	Haria	16-Aug-67	Male	Married	Learning & Development	South	37000​
150888	Gururaj	Joshi	01-Jan-80	Male	Married	Learning & Development	East	43000​
150865	Ruffina	Joshi	20-Aug-85	Female	Married	CEO	East	90000​
150858	Jagjit	Kahlon	27-May-95	Male	Married	CCD	East	34000​
150930	Piyush	Kamdar	16-May-01	Male	Married	Digital Marketing	South	82000​
150894	D	Kulkarni	21-Aug-01	Male	Married	Inside Sales	South	67000​
150947	Raju	Manek	30-Jul-91	Female	Married	CCD	South	85000​
150905	Yogesh	Mansharamani	17-May-84	Female	Single	FLM	South	62000​
150995	Satish	Pasari	22-Sep-96	Male	Married	Inside Sales	Mid West	15000​
150912	Nitin	Patki	08-Jan-03	Female	Married	Operations	South	81000​
150921	Prem	Pherwani	15-Apr-04	Male	Married	Finance	South	19000​
150851	Sudesh	Pillai	27-May-80	Male	Single	Inside Sales	East	75000​
150867	Boneca	Rego	22-Jun-79	Female	Single	Finance	East	49000​
150899	Sharadchandra	Riswadkar	24-May-02	Male	Married	CCD	South	50000​
150975	Simon	Rodrigues	07-Mar-86	Male	Married	Finance	Mid West	83000​
150901	Ashok	Samtaney	14-Mar-90	Female	Married	Sales	South	53000​
150968	Praful	Savla	13-Nov-01	Male	Married	Operations	South	65000​
150773	Stan	Serrao	15-Jul-73	Male	Married	Finance	North	85000​
150840	Piyush	Shah	05-May-63	Female	Married	Inside Sales	East	20000​
150850	Dhiren	Sheth	07-Sep-87	Male	Married	CCD	East	47000​
150962	Shankar	Shetty	01-Jun-03	Female	Married	Director	South	87000​
150954	Kawdoor	Shetty	06-Mar-97	Female	Married	Sales	South	57000​
150874	Venitha	Shetty	26-Sep-03	Female	Married	Marketing	East	27000​
150798	Tulsidas	Shetty	31-May-77	Female	Married	Digital Marketing	North	81000​
150830	Rajeev	Singh	01-Jul-79	Female	Married	Sales	North	52000​
150929	Bobby	Tanna	16-Mar-73	Male	Married	Marketing	South	58000​
150982	Jitendra	Thacker	24-May-97	Male	Married	Marketing	Mid West	47000​
150821	Yashraj	Vaidya	15-Jan-82	Male	Single	CCD	North	26000​

 

[arthurbr]

Salaries can be obtained with MIN and MAX functions. Embedding these functions in the MATCH part of the NDEX/MATCH combination allows to retrieve the employee's name

 

[Peter_SSs]

The problem with using match comes if more than one person has equal max or min salary.
Here is a method for your version that will return all names if equals exist.
Note that I have changed the sample data so that there is an equal max salary.

23 03 06.xlsm
	B	C						I	J	K	L	M	N	O
1	FirstName	LastName						Basic Salary
2	Ram	Ambradkar						48000		Max Salary	92000	Dinesh Dhanuka	Nitin Patki
3	Sachin	Bangera						35000		Min Salary	15000	Satish Pasari
4	Rajesh	Bohra						67000
5	Rajeesh	C						87000
6	Melwyn	Crasto						22000
7	Rajesh	Dedhia						91000
8	Dattatray	Desai						77000
9	Vishnu	Desai						45000
10	Dinesh	Dhanuka						92000
11	Heena	Dongre						50000
12	Dhiren	Haria						37000
13	Gururaj	Joshi						43000
14	Ruffina	Joshi						90000
15	Jagjit	Kahlon						34000
16	Piyush	Kamdar						82000
17	D	Kulkarni						67000
18	Raju	Manek						85000
19	Yogesh	Mansharamani						62000
20	Satish	Pasari						15000
21	Nitin	Patki						92000
22	Prem	Pherwani						19000
23	Sudesh	Pillai						75000
24	Boneca	Rego						49000
25	Sharadchandra	Riswadkar						50000
26	Simon	Rodrigues						83000
27	Ashok	Samtaney						53000
28	Praful	Savla						65000
29	Stan	Serrao						85000
30	Piyush	Shah						20000
31	Dhiren	Sheth						47000
32	Shankar	Shetty						87000
33	Kawdoor	Shetty						57000
34	Venitha	Shetty						27000
35	Tulsidas	Shetty						81000
36	Rajeev	Singh						52000
37	Bobby	Tanna						58000
38	Jitendra	Thacker						47000
39	Yashraj	Vaidya						26000
Salaries

Cell Formulas
Range	Formula
L2	L2	=MAX(I2:I39)
M2:O3	M2	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$40)/($I$2:$I$40=$L2),COLUMNS($M:M)))&" "&INDEX($C:$C,AGGREGATE(15,6,ROW($B$2:$B$40)/($I$2:$I$40=$L2),COLUMNS($M:M))),"")
L3	L3	=MIN(I2:I39)