index match two vertical one horizontal

Hi

I've tried all the advice on this forum but for some reason my formula is not working ....

I want to look up the Reporting Theme and Customer Type in the Sales Rev worksheet and match it with the vertical columns in the Revenue Asumption Input worksheet. Once this is true I want it to look up the Subproduct distribution platform in the Sales Rev Worksheet and bring back the horizontal header match in the Revenue Assumption Input Worksheet.

These are two formulas I have tried...plus many more.....

=INDEX('Revenue Assumption Input'!F6:V25,MATCH(TRUE,IF('Revenue Assumption Input'!D6:D26='Sales Rev 15-16 WRC Current'!B5,'Revenue Assumption Input'!E6:E26='Sales Rev 15-16 WRC Current'!C5),0),MATCH('Sales Rev 15-16 WRC Current'!D5,'Revenue Assumption Input'!D5:V5,0))
=INDEX('Revenue Assumption Input'!F6:V26,MATCH('Sales Rev 15-16 WRC Current'!D6,'Revenue Assumption Input'!D5:V5,0),MATCH(1,INDEX(('Sales Rev 15-16 WRC Current'!B6='Revenue Assumption Input'!D6:D26)*('Sales Rev 15-16 WRC Current'!C6='Revenue Assumption Input'!E6:E26),0),0))

I'm getting myself totally confused....
I tried to attach a file with the extract of the two worksheets....but it wont work....Can anyone help ??

otherwise the data below might help



Sales Rev 15-16

B C D E
Reporting Theme Customer Type Sub Product Distribution Platform Sum of Revenue Sum TFY
Aerial Imagery Consumer P2P $187
Aerial Imagery Consumer Shopfront $118,984
Aerial Imagery Consumer Digital Data $256,502
Aerial Imagery landgate Shopfront $0
Aerial Imagery Retailer Shopfront $1,302
Aerial Imagery Retailer Digital Data $48,875
Aerial Imagery Wholesaler Digital Data $6,843




D E F G H I J

Reporting Theme Customer Type Lodgement LEN EAS2 MapViewer Shopfront
Aerial Imagery TRUE 20%
Aerial Imagery TRUE 30%
Aerial Imagery Wholesaler 40%
Aerial Imagery Retailer 50% 50%
Aerial Imagery Consumer 60%

What's your expected answer in the two gold-coloured cells?

Grrr. I spent ages wondering what I was always getting 75% as the answer to everything. You had calculation options set to manual....

=SUMPRODUCT((B5='Revenue Assumption Input'!$D$6:$D$264)*('Sales Rev 15-16 WRC Current'!C5='Revenue Assumption Input'!$E$6:$E$264)*('Sales Rev 15-16 WRC Current'!D5='Revenue Assumption Input'!$F$5:$V$5)*'Revenue Assumption Input'!$F$6:$V$264)

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.