Double Variable Lookup (Think SUMPRODUCT would work)

[Jay_hl]

All

In essence this problem has three parts.

1 the input. Here line items costs are submitted which need to be "paid for" and split by 5 different businesses.

2 the businesses need to pay in different shares depending on a "key". This is static in a lookup table.

3 the output. I need to see per line item category, the total each business needs to pay after their % split has been summed up.

As always have tried to mockup the data, and the expected outcome. Allocation Key Lookup.xlsx

Appreciate the advise/any solutions

Jay

[Fotis1991]

I don't have time to search this but looks that this formula to P4 and copy across works.

Please Login or Register  to view this content.

[Jay_hl]

Thanks for the reply. Appreciate you taking the time.

I have tried and although it works for the first line, doesnt give any results when I drag it down to Category B and C

Also if I change the Key from 1 to 2 (in D4) for the first line item, it seems to not give any results anymore

Jay

[newdoverman]

Maybe this solution will work for you. I created totals for each category and key per business then summarized the data using SUBTOTAL and also a Pivot Table.

[Jay_hl]

Thanks NDM. I will use this in the interim so I can get on with the analysis, but with lots of Keys, and Categories I was hoping for a SUMPRODUCT solution like Fotis produced.

Any other SUM PRODUCT gurus out there?

Jay

[Fotis1991]

Apologize for the delay! As i said, too much work for me today.....

One helper(& hidden column) in this solution.

In P4 & copy down.

Please Login or Register  to view this content.

Then in Q4 and copy down and across.

Please Login or Register  to view this content.

[Jay_hl]

Sorry to be a pain here Fotis, but its not giving the right answer when I look at the business splits. Long handed I calculate that Business 1 should total 138, whereas the solution you so kindly provided totals 98.

Each line item can use a different key, so a category has more than 1 key, which is why I thought it had to be an array with SUMPRODUCT, and not just an INDEXMATCH to look up the correct allocation key.

Jay

[newdoverman]

Here is the beginnings of what could be an awful formula if there are more categories, keys and companies. Enter in P4 and fill across and down.

Please Login or Register  to view this content.

[daddylonglegs]

Assuming the businesses will always be in the same order in H3:L3 as P3:T3 you can use this formula in P4 copied across and down

=SUMPRODUCT(($B$4:$B$10=$O4)+0,SUMIF($G$4:$G$7,$D$4:$D$10,H$4:H$7),$C$4:$C$10)

If the businesses might be in a different order you can use this version to explicitly look up the business and get the correct key percentages

=SUMPRODUCT(($B$4:$B$10=$O4)+0,SUMIF($G$4:$G$7,$D$4:$D$10,INDEX($H$4:$L$7,0,MATCH(P$3,$H$3:$L$3,0))),$C$4:$C$10)

[Jay_hl]

Fabulous. Have made my day. Thanks DLL!

Jay