Using index(match) to look up a multiplication value

[Andy959]

All,

I have a table with 1,000 plus lines where I need to normalize values to a yearly total. In the example below, sheet1 one has a column with a bunch of text. I'd like to look up the left 4 characters and match that to the table on sheet2 which contains a multiplier to normalize the data for 1 year. Basically anything with 3 MO (3 months) should be multiplied by 4. So the expected results column is where I'd enter the formula to get the 4x2 with the 2 coming from the duration column. I have about 20 different instances on Sheet2 to lookup against.

Any thoughts on a formula to make this repeatable?

Sheet1

Process	Duration	EXPECTED RESULT
3 MO PM	2	8
2 YR PM	1	.5

<tbody>
</tbody>

Sheet2

3 MO	4
2 YR	.5
1 YR	1

<tbody>
</tbody>

 

[gaz_chops]

Try

=INDEX($B$7:$B$9,MATCH(LEFT(A3,4),$A$7:$A$9,0))*B3

Adjust the ranges to fit your data ranges.

[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]Sheet1[/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
 [/TR]
 [TR]
  [TD]Process[/TD]
  [TD]Duration[/TD]
  [TD="colspan: 2"]EXPECTED RESULT[/TD]
 [/TR]
 [TR]
  [TD]3 MO PM[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]8[/TD]
  [TD="align: right"]8[/TD]
 [/TR]
 [TR]
  [TD]2 YR PM[/TD]
  [TD="align: right"]1[/TD]
  [TD="align: right"]0.5[/TD]
  [TD="align: right"]0.5[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Sheet2[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]3 MO[/TD]
  [TD="align: right"]4[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]2 YR[/TD]
  [TD="align: right"]0.5[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]1 YR[/TD]
  [TD="align: right"]1[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[Andy959]

[COLOR=#000000][FONT=Lucida Grande]=INDEX([COLOR=#0057d6]$B$7:$B$9[/COLOR],MATCH[COLOR=#006107]([/COLOR]LEFT[COLOR=#ab30d6]([/COLOR][COLOR=#006107]A3[/COLOR],4[COLOR=#ab30d6])[/COLOR],[COLOR=#ab30d6]$A$7:$A$9[/COLOR],0[COLOR=#006107])[/COLOR])*[COLOR=#a54a29]B3

Adjust the ranges to fit your data ranges.[/COLOR][/FONT][/COLOR]

Works,thanks.

 

[gaz_chops]

You're welcome.