CODEWITHSUNDEEP
excel
PolGraphic
PolGraphic

Reputation: 3364

Finding the cell value from another sheet basing on row/column index

I use Excel 2013.

I have two sheets: work_hours and tasks:

#work_hours!
    A            B        C            D
1   Task         Hours    Cost (sum)   Cost per hour
2   Task1        5
3   Task2        3
4   Task1        2

#tasks!
    A            B            C
1   Name         Cost (sum)   Description
2   Task1        500          Desc1
3   Task2        1000

I want to fill the work_hour:C column (Cost (sum)) automatically (by formula).

The value of each cell (e.g. C2, C3, C4... let's call it MY CELL) should be:

**MY CELL** value should be: The value of cell ($B;x) from `tasks` sheet, 
where x is the row index in `tasks` where name (A column) match the name
from the row of **MY CELL**.

So in my example the formula should produce:

#work_hours!
    A            B        C            D
1   Task         Hours    Cost (sum)   Cost per hour
2   Task1        5        500
3   Task2        3        1000
4   Task1        2        500

I know that the row index from tasks can be found by:

=MATCH(C2;tasks!A:A;0)

Which returs 2.

But how to change that formula, so it will return 500 (value of B column in tasks with the same row index)?

Upvotes: 1

Views: 14169

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Try VLOOKUP like this

=VLOOKUP(C2;tasks!A:B;2;0)

That will match C2 with column A in Tasks and then return the value from the second column

Upvotes: 2

Related Questions