calculation to insert salary based on position title

[slsjmc]

I have an excel spreadsheet that will have approximately 11 position
titles within a company (e.g.: supervisor, foreman, labourer,
labourer with 10 yrs, labourer with 20 yrs, etc. I would like to
insert a formula to insert the wage automatically, based on the
position title that was inserted.

e.g.:Typing Supervisor in one column will automatically insert the
salary of $100,000.00 in the next column;
Foreman will automatically insert $75,000.00
Labourer $50,000.00
Labourer 10 yrs $55,000.00
Labourer 20 yrs $60,000.00

And so on. As previously stated, there will be approx. 11 positions,
although there could be more added at a later date.

I am a basic Excel user and would appreciate some help. Thank you in
advance.

 

[Gord Dibben]

On sheet2 enter titles in column A, salaries in column B

In sheet1 A1 enter a title

In B1 enter =VLOOKUP(A1,Sheet2!($A$1:$B$11,2,FALSE)

Might be an idea to create a Data Validation dropdown list in A1 to select
the title.

Give your Sheet2 titles range A1:A11 a defined name like "title_list"(no
quotes)

Select A1 on sheet1 and Data>Validation>Allow>List>Source =title_list


Gord Dibben MS Excel MVP

 

[AltaEgo]

Lets assume your lookup table is stored as follows:

- Titles A2:A5
- Salary B2:B5:


Assuming you want to look up the salary of a user choice in A8, the formula
to use is as follows:

=INDEX($A$2:$B$5,MATCH(A8,$A$2:$A$5,0),2)


To operate in your worksheet, change references as follows:

$A$2:$B$5 - the full list of your titles and salary
A8 - the cell reference for which you wish to locate a salary.


Here is a very good reference if you strike problems:
http://www.contextures.com/xlFunctions03.html


Hint: to prevent poor typing resulting in error, use data validation and
limit your job titles to a list.

Click menu item Data, Validation, change "allow:" to list, change "source:"
to the list of job titles (e.g. $A$2:$A$5).

 

[bostontj98]

I have an excel spreadsheet that will have approximately 11 position
titles within a company (e.g.:  supervisor, foreman, labourer,
labourer with 10 yrs, labourer with 20 yrs, etc.  I would like to
insert a formula to insert the wage automatically, based on the
position title that was inserted.

e.g.:Typing Supervisor in one column will automatically insert the
salary of $100,000.00 in the next column;
       Foreman will automatically insert $75,000.00
       Labourer $50,000.00
       Labourer 10 yrs $55,000.00
       Labourer 20 yrs $60,000.00

And so on.  As previously stated, there will be approx. 11 positions,
although there could be more added at a later date.

I am a basic Excel user and would appreciate some help.  Thank you in
advance.

You could also build a nested if/then formula. Column A would be th
title and a formula like this would go in column b:

=IF(A2="Foreman",100000,IF(A2="Labourer",50000,IF(A2="Labourer 20",
60000,25000)))

the values in quotes have to exaclty in quotes, and like Gord said, a
validation list is a good way to keep them consistent