# Using index(match) to look up a multiplication value

#### Andy959

##### Board Regular
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 0.5 1 YR 1

<tbody>
</tbody>

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### gaz_chops

##### Well-known Member
Try

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

Code:
``````[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

##### Board Regular
Code:
``````[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

Works,thanks.

Last edited:

You're welcome.

Replies
4
Views
129
Replies
8
Views
130
Replies
10
Views
109
Replies
3
Views
142
Replies
28
Views
234

1,191,687
Messages
5,988,019
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back