Multiple IF/LOOKUP-Problem!

[ScaniaR730]

Got little problem with multiple parameters, could anyone be kind enough and help me?

Check the attachment!

Thanks in advance!

[Tony Valko]

Try this array formula** entered in G10 and copied down:

=INDEX(E$4:G$8,MATCH(1,IF(B$4:B$8=B10,IF(C10>=C$4:C$8,IF(C10<=D$4:D$8,1))),0),MATCH(D10,E$3:G$3,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[XOR LX]

??

Is this like Jeopardy? You give some answers in a sheet plus some data and we have to guess what the formula could have been, and what data it applied to?

[Ace_XL]

In G10, try this array formula

=INDEX($E$4:$G$8,MATCH(1,($B$4:$B$8=B10)*($C$4:$C$8=INDEX($C$4:$C$8,MATCH(C10,$C$4:$C$8,1),0)),0),MATCH(D10,$E$3:$G$3,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Teethless mama]

Another one...

=SUMPRODUCT((B$4:B$8=B10)*(C$4:C$8<=C10)*(D$4:D$8>=C10)*(E$3:G$3=D10)*E$4:G$8)

Normally Enter