0

Suppose there are 2 excel tables with following information:

First table:

enter image description here

Second table:

enter image description here

The task is to find for each cost center an employee(allocate cost center to employee).

How can I do it in excel?

Improve this question

2 Answers 2

Reset to default
0

The easiest way would be to use a VLOOKUP You can look up a value from one table, and populate it in another field.

Improve this answer
0

You could use vlookup but this will require the index field (cost center) to be on the left of your return value (employee). If you swap B and C columns of the second table, you can use vlookup with this formula in F2 of the 1st table: =vlookup(E2, Table2!B2:Table2!C,2,FALSE)

Otherwise, this formula will work without changing the current tables. It uses the index, match method. Put this into F2 of the first table: =index(A:F, MATCH(E2, Table2!B:Table2!B,0),3)

The benefit of index match is that you can define what column you want to return by simply changing the return column value (3) above.

The limitation of both methods is that they don't handle multiple values well. You'll get only the first or last value depending on your implementation. Not sure if this is an issue, but SGRR70 has 3 employees in the example above.

Will need clarification if you're looking for a specific return value

Improve this answer
1
  • Yes, as you've noticed, SGRR70 has 3 employees and I was looking for all 3 results after filtering. If VLOOKUP doesn't work for multiple values, whta method can work?
    – Ray
    Apr 7, 2019 at 15:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .