# index match two vertical one horizontal

1. ## 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%

2. ## Re: index match two vertical one horizontal

look at
attachment0.jpg

3. ## Re: index match two vertical one horizontal

Thanks Sandy666 File now attached

5. ## Re: index match two vertical one horizontal

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)

6. ## Re: index match two vertical one horizontal

Thank you so much Glenn Kennedy !!! I am so sorry about the manual calc...it is part of a bigger model so I turn it off when I am writing formulas...

I really appreciate it! Hope you have a Happy Easter!!

Thanks again.

7. ## Re: index match two vertical one horizontal

You're welcome.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1