Values based on three inputs

Trying to look up values based on three inputs:

Attached is the file:

Month Input, Column Input and Network Input are changeable based on user input, I am trying to get the desired output based on selection from A20, B20, C20 into cells G20, G21, G22 from row number 2 and corresponding values in H20, H21, H22 from the corresponding row based on the selection.

I have populated an example in the attached file.

Any help will be greatly appreciated. Thanks in advance.

Try this. It assumes that the combination will be unique. If the same combination occurs more than once then you will get answers that don't make any sense.

G20:

=INDEX($C$2:$H$2,MATCH($C$20,C$3:F$3,0)+ROW(A1)-1)

and copy downwards

H20:

=SUMPRODUCT(($A$4:$A$16=$A$20)*($B$4:$B$16=$B$20)*($C$2:$H$2=$G20)*($C$4:$H$16))

and copy downwards

Glad I was able to help out. Thanks for the feedback.

Hi daffodil,

Can we do it without using "Row" function in G20, please. Thanks in advance.

G20:

=INDEX($C$2:$H$2,MATCH($C$20,C$3:F$3,0)+ROW(A1)-1)

Sure. They're just an efficient way to write 0, 1, 2.

G20:

=INDEX($C$2:$H$2,MATCH($C$20,C$3:F$3,0))

G21:

=INDEX($C$2:$H$2,MATCH($C$20,C$3:F$3,0)+1)

G22:

=INDEX($C$2:$H$2,MATCH($C$20,C$3:F$3,0)+2)

Sorry, I was grinding away on a Crystal Report. Those things are terrible. (and a little awesome)

G20:

=INDEX($C$2:$S$2,MATCH($C$20,C$3:S$3,0))

G21:

=INDEX($C$2:$T$2,MATCH($C$20,C$3:T$3,0)+1)

G22:

=IF(INDEX($C$3:$U$3,MATCH($C$20,C$3:U$3,0)+2)=C20,INDEX($C$2:$U$2,MATCH($C$20,C$3:U$3,0)+2),"")

H20:

=IF(G20="","",SUMPRODUCT(($A$4:$A$16=$A$20)*($B$4:$B$16=$B$20)*($C$2:$T$2=$G20)*($C$4:$T$16)))

and copied down

for shareq.xls

No problem. I always enjoy a good challenge.

One more Issue now:

I am unable to display the result if I use Index, Match, Sumproduct.

Can it be done using Vlookup or SUMIF or IF or Concat by any chance. Any help will be greatly appreciated. Thanks in advance.

Okay, to accommodate your request things get a little trickier.

First, I to insert a column in C:

=A4&B4 and copied down

Next I had to make a table to VLOOKUP the subcodes.

Then I added a VLOOKUP table to match subcode to column number.

Lastly, I had to add a column at the end of " - " so that when no subcode is found it doesn't make error.

shareq makes me work so hard today.xls

I also have to lookup the Network and Column Input as well, i.e cells A20, B20 and C20 and need to get the appropriate row into the output field. Can you please check. Thanks.

Triumph at last!

Thanks for making me work for it.

Don't forget to mark the thread as "Solved" under the Thread Options at the top.

The only thing I found missing was to change each formula in I20:I22 from ..)) to ),0) so that it correctly produces an error when the data isn't found.

I can't find any other issues. Can you attach the copy you're using and highlight the error?

What are you entering into A20:C20?

That's corrected with what I suggested a few posts ago. It was missing the ,0) at the end.

I20:

=VLOOKUP($A$20&$B$20,$C$4:$V$16,VLOOKUP($H20,$Q$21:$R$39,2,0),0)

I21:

=VLOOKUP($A$20&$B$20,$C$4:$V$16,VLOOKUP($H21,$Q$21:$R$39,2,0),0)

I22:

=VLOOKUP($A$20&$B$20,$C$4:$V$16,VLOOKUP($H22,$Q$21:$R$39,2,0),0)

You have 2 month outputs. They can't both be true at the same time.

I can't specify Month Input 1, and then at the same time Month Input 12, 11, 10 , 9..