Look up and Replace

[Kim R]

I am trying to popluate worksheet 1 with $ amounts. I want to do this by
looking up an Initial in worksheet 3 and multiplying it by the hours in
worksheet 2. The intial's and column headings will need to be equal to
account for escalation over a period of years.

For example if CB=March 09 then multiply $10.00 * 50 Hours and popluate this
into Worksheet 1.... and so on over a period of years

Sample Data:

Worksheet 1

Initial March 09 March 10 March 11
1 CB
2 AC
3 CC
4 ST
Worksheet 2

Initial March 09 March 10 March 11
1 CB 50hours 30hours 40hours
2 AC 50hours 30hours 40hours
3 CC 50hours 30hours 40hours
4 ST 50hours 30hours 40hours


Worksheet 3

Initial March 09 March 10 March 11
1 CB $10.00 $20.00 $30.00
2 AC $10.00 $20.00 $30.00
3 CC $10.00 $20.00 $30.00
4 ST $10.00 $20.00 $30.00

 

[Dave Peterson]

You can use =index(match(), match()) to return each value.

Debra Dalgleish explains it here:
http://contextures.com/xlFunctions03.html#IndexMatch2

To get the number of hours, you could use something like:
=index(sheet2!$a$1:$d$99,match($a2,sheet2!a:a,0),match($b1,sheet2!1:1,0))

And to get the rate
=index(sheet3!$a$1:$d$99,match($a2,sheet3!a:a,0),match($b1,sheet3!1:1,0))

To get the extended amount, just multiply them:

=index(sheet2!$a$1:$d$99,match($a2,sheet2!a:a,0),match($b1,sheet2!1:1,0))
*index(sheet3!$a$1:$d$99,match($a2,sheet3!a:a,0),match($b1,sheet3!1:1,0))

You'll have to adjust the ranges to match your data.