Look Up value across 2 sheets

[Nikkic]

Hi I have two tabs on one workbook, as shown below. I need to look up 'Planned Cost' from Tab 2 into Tab 1 for each month .Actual cost is an manual entry. Planned cost changes every month.


Tab 1:

[TABLE="class: outer_border, width: 428, align: center"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="colspan: 2, align: center"]Jan-13[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]Planned Cost[/TD]
[TD]Actual Cost[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Apple[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"](5)[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Orange[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]



Tab 2:


[TABLE="class: outer_border, width: 517, align: center"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: center"]Jan-13[/TD]
[TD] [/TD]
[TD="align: center"]Jan-13[/TD]
[TD] [/TD]
[TD="align: center"]Jan-13[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD]Planned Cost[/TD]
[TD] [/TD]
[TD]Planned Cost[/TD]
[TD] [/TD]
[TD]Planned Cost[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Apple[/TD]
[TD="align: right"]$10[/TD]
[TD] [/TD]
[TD="align: right"]$15[/TD]
[TD] [/TD]
[TD="align: right"]$20[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Orange[/TD]
[TD="align: right"]$20[/TD]
[TD] [/TD]
[TD="align: right"]$20[/TD]
[TD] [/TD]
[TD="align: right"]$25[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sinon]

Please be more specific. On what basis do you want to return the Planned cost? Is your criteria solely Apple, Orange etc? Which columns should the cost be returned from as you have 3 Planned cost columns in Tab 2. Is it always the first one? You also have a date there, does that matter in any way?
It sounds like you might simply need a lookup but without knowing how many criteria you have its difficult to provide it.
Try VLOOKUP for one criteria: https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Try INDEX/MATCH for multiple criteria: How to VLOOKUP with Multiple Criteria Using INDEX and MATCH in Excel
You can further search google for any of the 2. Looking on some youtube videos might help you understand them better, especially INDEX/MATCH which is a bit more complex than VLOOKUP.

 

[Nikkic]

Please be more specific. On what basis do you want to return the Planned cost? Is your criteria solely Apple, Orange etc? Which columns should the cost be returned from as you have 3 Planned cost columns in Tab 2. Is it always the first one? You also have a date there, does that matter in any way?
It sounds like you might simply need a lookup but without knowing how many criteria you have its difficult to provide it.
Try VLOOKUP for one criteria: https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Try INDEX/MATCH for multiple criteria: How to VLOOKUP with Multiple Criteria Using INDEX and MATCH in Excel
You can further search google for any of the 2. Looking on some youtube videos might help you understand them better, especially INDEX/MATCH which is a bit more complex than VLOOKUP.

Hi Sinon: Look up value based upon Apple,Orange etc. and the date.

Scenario: On Tab 2 we maintain planned cost of Apple oranges by each month.
On Tab 1 - I pull the cost based upon month and name of item, manually enter Actual cost, then delta is calulated based on the difference.