Combining 2 Index Match functions!

[Hellion]

Hello guys!

I have the following table:

test.jpg

My ultimate goal is to derive a formula on column "V" (TOTAL in green) to give me the results
I have there and are based on the following premises:

- Companies A and B have to allocate 100% of the costs of their own employees plus the percentage
imported from the other company (the other company exported to it). These costs have to be allocated
on the Cost Centres CC-A1 to CC-A5 (if company A) or CC-B1 to CC-B5 (if company B) in row 3.

- When a company export some costs, it also has to allocate the same percentages on the "mirror" Cost Centres
in row 4 (CC-A101 to CC-A105 and CC-B101 to CC-B105).

Hence, I need a formula to give me (in column "V"): All costs allocated on table "Company A Cost Centres
Allocation" plus whatever is allocated on table "Company B Cost Centres Allocation" ONLY when column E
(export to B) is different than 0%.

Likewise, the formula should also cover the counterpart situation which is: All costs allocated on table
"Company B Cost Centres Allocation" plus whatever is allocated on table "Company A Cost Centres Allocation"
ONLY when column M (export to A) is different than 0%.

This would be something like:

=(IFERROR(INDEX($F$16:$J$16,MATCH($U3,$F$3:$J$3,0)),"0")) + (IFERROR(INDEX($N$16:$R$16,MATCH($U3,$N$18:$R$18,0)),"0"))

Obviously the "+" is not right. I tried to concatenate these the 2 statements above with several functions but for
whatever reason I can't make it work. Once I have it concatenated it wouldn't be difficult to repeat if for the
counterpart using column C (values A or B) as logical test in an IF function but my problem is really the sintax
to put the two togheter.

Regards,

Ricardo

[Richard Buttrey]

Hi,

You'd be better advised to upload the workbook so that we have something to work with. Pictures are rarely much use.

[Pete_UK]

You should change the ,"0" to ,0 in both expressions.

Hope this helps.

Pete

[Hellion]

Richard,

I tried to attach it a first place but when I click the Attachment button the only thing that appears is a very thin white strip and not a box allowing me to browse the file. That's why I uploaded a picture. What am I doing wrong?

Regards,

Ricardo

[Richard Buttrey]

Hi

If Pete's answer has not helped you then see here for attaching....FAQ How to attach a file

[Pete_UK]

I have found that the FAQ description is a bit out of date, and not very detailed, so here's what to do to attach a file:

Click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and you should see your filename listed on screen. Click on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

[Hellion]

Guys,

Thank you for your replies and I'm sorry for the long delay answering this but shortly after that I went on Holidays and sincerely forgot to do it.

I figured out what was happening. I don't know why but when I was writing the formulae I left a space before and after the "+" sign. Tiredness, i guess.

Just for reference, the expressions work with both forms, 0 and "0", maybe because the cell where the formula is contained is formatted to percentages and it transforms it into numbers automatically.

Regards,

Ricardo