Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

By example:

table1

Position     Salary
Seller       150
Management   200
Technical    120
Maintenance  100

table2 (in same sheet or in a different sheet that doesn't matter)

Name     Position          salary
John     Seller             ?
Paul     Technical          ?
Julia    Management         ?
Martha   Seller             ?
George   Technical          ?
Ringo    Maintenance        ?

How can I put the salary corresponding in third column?

I've already selected Data->Validation->List for Position in table2, But how can I fill the "salary" column automatically?

Thank you for any help.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Do a VLOOKUP

=VLOOKUP(B2,Sheet2!$A$2:$B$5,2, FALSE) Given your stated tables, assuming the first one is Sheet2 and your second is Sheet1.

share|improve this answer

As your question is about Excel-table;

In Excel when you select a list and apply Format as Table on it; that range automatically takes a name, that you can find it in [TABLE TOOLS] DESIGN additional tab, in the box of Table Name, like Table1 and you can change it to your favorite name.

You can use the name of that table for reaching columns and etc, like:

Table1  --> All columns and all rows in the table criteria.
Table1[Col1]  --> Range of column of 'col1' from the below row of the header to last row of table.
Table[[Col1]:[Col3]] --> Range of columns between 'col1' to 'col2'.

=[@[Col1]] --> This formula returns the value of `Col1` from current row of that column.

Recommendation:
When you use this range specifying changes to table don't harm your related formulas.


So, you can use that ranges in your formulas like:

=VLOOKUP([@Position];Table1[[Position]:[Salary]];2;FALSE)

or (recommended):

=INDEX(Table1[Salary]; MATCH([@Position];Table1[Position];0))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.