Using Vlookup to find pay rate for employees

[Jhmtaylor]

Our company operates in 2 countries with different rates of pay for personnel in each.

Part of my spreadsheet requires the looking up of pay rate based on the employees occupation and the country. I have been try to do this with Vlookup but I am beginning to believe this is not the correct function. The spreadsheet contains a cell with the employees countryand I would like to use value in this field to determine which pay scale to use.

Suggestions would be sincerely appreciated

 

[jaya]

In theory, you should be able to use Vlookup for this. Can you post a part of your worksheet?

 

[Jhmtaylor]

Sorry about this. How do I post the data and retain the formatting?

Employee Occupation Country Annual salary
A Look up Table
B Pay Scale
C
Australia China
Labourer 28,000 2,000
Clerk 32000 2,500
Junior Manager 40,000 4,000
Senior Manager 75,000 12,000
Director 120,000 40,000

 

[jaya]

Do you have the employee category in Column A and the corresponding slaray scales for Australia and China in columns B & C respectively?

Where do you want the results to appear?

Do you have a seperate list of employees with their occupations and country?

 

[Jhmtaylor]

Column A Starting at A3 is for the employee names
Column B Starting B3 is a validation data list for the respective occupations
Column C stating C3 is the employees country (validation data list)
Columns G3 to I8 contain the occupation label and the two pay scales

 

[Brian from Maui]

Looks like Index/Match

Book1
	A	B	C	D	E	F	G
1				Emp	Pay	Occupation	Country
2		Australia	China	A	2500	Clerk	China
3	Labourer	28000	2000	B	120000	Director	Australia
4	Clerk	32000	2500	C	2000	Labourer	China
5	Junior Manager	40000	4000
6	Senior Manager	75000	12000
7	Director	120000	40000
Sheet1

 

[Jhmtaylor]

Thanks for that. Index match does not mean much to me (yet) I have only just learnt VLOOKUP but I will study it and trial it over the next few days. But what you have done looks right. Thanks again