Let's assume the following...

A2 contains A/BBB/CA

B2 contains 20/30/50

C2 contains 50

Let E1:G1 contain A, BBB, and CA

Now, define the following...

1) Select E2

2) Insert Name Define

Name: CostPool

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$A2,"/",""",""")&"""}")

Click Add

Name: CostPoolSplit

Refers to:

=EVALUATE("{"""&SUBSTITUTE(Sheet1!$B2,"/",""",""")&"""}")

Click Ok

Lastly, try...

E2, copied across:

=$C2*INDEX(CostPoolSplit,MATCH(E$1,CostPool,0))/100

Note that the formula can also be copied down, if needed.

Hope this helps!

In article ,
Eva wrote:

Hi
I wonder if somebody can help me. I need to find a formula that finds which
cost pool split to apply to each cost pools.
Example:

Cost Pool Cost Pool Split Amount A BBB CA
A/BBB/CA 20/30/50 50 =amount*cost pool split
If I use search function it find me position of cost pool only
FE=SEARCH("bbb",A2,1)
I need to check position to "/" to find which cost pool split to apply
Eva