name range and Vlookup

[shreerajpatil16]

Assignment 2.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2			Get Region, Department and Salary of all Employees from Sheet "Source"
3			If C-Code is not present, display "Retired"
4			*Use Name Range and Vlookup with Match Function
5
6		C_Code	FirstName	LastName	Birthdate	Gender	M_Status	Region	Department	Salary
7		150834	Ram	Ambradkar	1-Jun-85	Female	Married
8		150784	Sachin	Bangera	28-Aug-77	Female	Single
9		150791	Rajesh	Bohra	1-Dec-63	Female	Married
10		150940	Rajeesh	C	30-Aug-73	Male	Single
11		150777	Melwyn	Crasto	30-Oct-57	Male	Married
12		150805	Rajesh	Dedhia	27-Aug-71	Male	Married
13		150990	Dattatray	Desai	28-Aug-99	Male	Married
14		150989	Vishnu	Desai	28-Aug-90	Male	Married
15		150881	Dinesh	Dhanuka	21-Jan-83	Male	Single
16		150814	Heena	Dongre	9-Nov-71	Male	Married
17		150937	Dhiren	Haria	16-Aug-67	Male	Married
18		150888	Gururaj	Joshi	1-Jan-80	Male	Married
19		150865	Ruffina	Joshi	20-Aug-85	Female	Married
20		150858	Jagjit	Kahlon	27-May-95	Male	Married
21		150930	Piyush	Kamdar	16-May-01	Male	Married
22		150894	D	Kulkarni	21-Aug-01	Male	Married
23		150947	Raju	Manek	30-Jul-91	Female	Married
24		150905	Yogesh	Mansharamani	17-May-84	Female	Single
25		150995	Satish	Pasari	22-Sep-96	Male	Married
26		150912	Nitin	Patki	8-Jan-03	Female	Married
27		150921	Prem	Pherwani	15-Apr-04	Male	Married
28		150851	Sudesh	Pillai	27-May-80	Male	Single
29		150867	Boneca	Rego	22-Jun-79	Female	Single
30		150899	Sharadchandra	Riswadkar	24-May-02	Male	Married
31		150975	Simon	Rodrigues	7-Mar-86	Male	Married
32		150901	Ashok	Samtaney	14-Mar-90	Female	Married
33		150968	Praful	Savla	13-Nov-01	Male	Married
34		150773	Stan	Serrao	15-Jul-73	Male	Married
35		150840	Piyush	Shah	5-May-63	Female	Married
36		150850	Dhiren	Sheth	7-Sep-87	Male	Married
37		150962	Shankar	Shetty	1-Jun-03	Female	Married
38		150954	Kawdoor	Shetty	6-Mar-97	Female	Married
39		150874	Venitha	Shetty	26-Sep-03	Female	Married
40		150798	Tulsidas	Shetty	31-May-77	Female	Married
41		150830	Rajeev	Singh	1-Jul-79	Female	Married
42		150929	Bobby	Tanna	16-Mar-73	Male	Married
43		150982	Jitendra	Thacker	24-May-97	Male	Married
44		150821	Yashraj	Vaidya	15-Jan-82	Male	Single
45
Master Emp sheet

.

Assignment 2.xlsx
	B	C	D	E	F	G
3
4
5		C_Code	Department	Region	Basic Salary
6		150773	Finance	North	85000
7		150777	Marketing	North	22000
8		150784	Digital Marketing	North	35000
9		150791	Digital Marketing	North	67000
10		150798	Digital Marketing	North	81000
11		150805	Director	North	91000
12		150814	Inside Sales	North	50000
13		150821	CCD	North	26000
14		150830	Sales	North	52000
15		150834	FLM	North	48000
16		150840	Inside Sales	East	20000
17		150850	CCD	East	47000
18		150851	Inside Sales	East	75000
19		150865	CEO	East	90000
20		150867	Finance	East	49000
21		150874	Marketing	East	27000
22		150881	Digital Marketing	East	92000
23		150888	Learning & Development	East	43000
24		150894	Inside Sales	South	67000
25		150901	Sales	South	53000
26		150905	FLM	South	62000
27		150912	Operations	South	81000
28		150921	Finance	South	19000
29		150929	Marketing	South	58000
30		150930	Digital Marketing	South	82000
31		150937	Learning & Development	South	37000
32		150940	Inside Sales	South	87000
33		150947	CCD	South	85000
34		150962	Director	South	87000
35		150968	Operations	South	65000
36		150975	Finance	Mid West	83000
37		150982	Marketing	Mid West	47000
38		150989	Digital Marketing	Mid West	45000
39		150990	Learning & Development	Mid West	77000
40		150995	Inside Sales	Mid West	15000
41
Source

 

[etaf]

is this homework/assignment ?

 

[Meow1311]

Same question sloution i want

 

[etaf]

as i said here

my assignment issues

please can someone help me with my assignment, i 'am unable to solve it.

www.mrexcel.com

I can help you and point in the right direction

if you have posted the question on other forums - then please let us know.....

the common key between those 2 sheets appears to be the c_code column, as mentioned in the question

so you should be able to use a lookup to do that - quite a few different functions for lookups

vlookup
using a combination of index and match
Xlookup

so you should be able to research and find how to use those
its asking you to use vlookup , so you should be able to find plenty of example on the web telling you how to do that

you should also search the web for if there is an error - ie the employee is not found and vlookup will return N/A - how to handle that , again there is a function in excel to do that

hope that helps - but I do not do the assignment here , thats for you to learn , and research , i will only point you in the right direction

I assume you have tried looking up vlookup and how to setup named ranges and got stuck
if so explain what you have tried and why stuck , results you have got so far