Match And Index Formula To Lookup Data From Table

[srizki]

In column A, I have account numbers like, 850032, 840055, 862212 etc. and column B house amount. I have a table containing, 5 rows, in that, the first column has account numbers, like in the main table, and second column says, the percentage. I want a formula in the column C of main table, that matches the account numbers and then multiply the amount by the percentage of lookup table's corresponding cell.
Lookup table

% to be allocated to Operating exp % to be allocated to Maintenance exp Maintenance account
856001 73% 27% 863
853000 68% 32% 864
857000 70% 30% 865
850001 80% 20% 861
859000 80% 20% 867

Also, as it can be seen the last column has 863, 864, 865 etc. in another column say, column D, I want a formula that matches the account numbers and replace the first 3 digits by the corresponding digits.
So, 856001 will become 863001, 853000 will become 864000 and so on.
Here is a small part of the main table.


SAP_Account Amount
8500005 4,529.49
8500005 5,500.10
8500005 12,096.11
8500100 132.08
8500121 445.00
8500203 78.84
8500211 153.99
8500231 128.35
8500232 1,045.70
8500271 750.00
8500275 533.95
8500308 63,528.95

Thanks

[Admin]

Hi

in C2

=SUMIF($I$2:$I$13,A2,$J$2:$J$13)*B2

In D2

=(D2&MID(A2,4,10))+0

where;

$I$2:$I$13 = SAP_Account
$J$2:$J$13 = Amount

[srizki]

Thank you very much. I will that now.

[srizki]

Thank you, the formula is excellent, but I just realized that we match only fiest 3 digits of both matchings. How can I add =left(A2,3) function, so that it matches first 3 digit on both sides.

Thanks again.

[Excel Fox]

=SUMPRODUCT((LEFT($I$2:$I$13,3)=LEFT(A5,3))*$J$2:$ J$13)*B5

[srizki]

Thank you very much.The formula is excellent, easy to understand and working great.
Can I also get the other formula Please, that would replace the first three digits as it described in my original question.
that if the first 3 digits match then replace as it is in short table.

Thanks again.

[Admin]

Can I also get the other formula Please, that would replace the first three digits as it described in my original question.
that if the first 3 digits match then replace as it is in short table.

Thanks again.

See my second formula.

[srizki]

Okay, Thanks

But how would your second formula know that what should replace, it needs to match before, right?
% to be allocated to Operating exp % to be allocated to Maintenance exp Maintenance account
8560001 13% 27% 863
8530000 18% 32% 864
8570000 70% 30% 865
8500001 10% 20% 861
8590000 10% 20% 867

so, the formula should find for example 856 and then replace first three to 863.