Index/Match to return array constant instead one cell/value

[Greentree]

Hello Everyone,
I am asking on some topic which is probably not new, so I did some research, yet my problem didnt solve.
Another topic which comes close is this one: http://www.mrexcel.com/forum/excel-questions/496921-return-array-index-match.html
Not sure if this would have to be placed in the trackback field.

I have a data set with quotation data, each on a specific date and I want to do some statistics with it.
For that purpose, I want to dynamically select certain date ranges from/to or other criteria from a source sheet and then feed those via an array constant into another output formula, in as few steps as possible, preferable as 1-step. The goal is to avoid manual steps here. I will apply this to much bigger data sets and query variations.

I am using this input formula at the moment
{=INDEX(Source!$A$2:$A$6;MATCH(1;(Source!$A$2:$A$6>=Forecast!$B$1)*(Source!$A$2:$A$6<=Forecast!$B$2)*(Source!$E$2:$E$6="Won");0))}

Forecast B1 and B2 sets the start and finish date of the query.
When clicking on the fx-button and going into the formula helper, it actually shows the right array constant (dynamic selection) only as a result INDEX picks a single value out of it.

I trying to feed the array constant into a statistical formula such as quartile.inc({=INDEX(Source!$A$2:$A$6;MATCH(1;(Source!$A$2:$A$6>=Forecast!$B$1)*(Source!$A$2:$A$6<=Forecast!$B$2)*(Source!$E$2:$E$6="Won");0))};2)

Ideally it should look like
Forecast!B1= 1-1-2013
Forecast!B2= 1-12-2014

WRONG =quartile.inc (3465;2)
RIGHT=quartile.inc({234,3465,856,345};2)
RIGHT Return Value = 600,5
The problem is that INDEX returns only one cell/value.

Thanks for your help.

Example Source:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Closed[/TD]
[TD]Customer[/TD]
[TD]Country[/TD]
[TD]Revenue[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1-7-2013[/TD]
[TD]A[/TD]
[TD]USA[/TD]
[TD]234[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]1-6-2014[/TD]
[TD]B[/TD]
[TD]UK[/TD]
[TD]3465[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]20-10-2014[/TD]
[TD]C[/TD]
[TD]Ger[/TD]
[TD]856[/TD]
[TD]Won[/TD]
[/TR]
[TR]
[TD]11-12-2014[/TD]
[TD]C[/TD]
[TD]Ger[/TD]
[TD]345[/TD]
[TD]Lost[/TD]
[/TR]
[TR]
[TD]3-3-2015[/TD]
[TD]A[/TD]
[TD]USA[/TD]
[TD]6786[/TD]
[TD]Won[/TD]
[/TR]
</tbody>[/TABLE]

 

[Mackers]

Hi

You can just use a single IF() as an array function (control+shift+enter) to return an array based on criteria, and then stick it in whatever formula you like.

=IF((Source!$A$2:$A$6>=Forecast!$B$1)*(Source!$A$2:$A$6<=Forecast!$B$2)*(Source!$E$2:$E$6="Won"),Source!$D$2:$D$6)

I changed the return to column D as I think you want the Revenue, right? In general:

=IF((DateRange>StartDate)*(DateRange<=EndDate)*(SourceRange="Won"), RevenueRange)

Hope that helps

Mackers

 

[Greentree]

Hi Mackers,
thank you very much. Its working with IF.
So simple it is! Unbelievable.
We have a saying: You cant see the wood among for all those trees anymore.

Thank you once more.

Mike

 

[Mackers]

Hi

I'm exactly in your camp there - the first thing I was thinking was "maybe I could return the entire row/column by using a 0 as an argument within the INDEX, or doing some kind of array multiplication". Then I took a step back and realised what was going on, hah.

Incidentally I hadn't thought to use your method of multiplying the arrays like you would in a sumproduct - I would've used IF(Array=Thing, IF(Array=Thing, IF(Array=Thing, DesiredArray))) which is less efficient and messier, so thanks.

Mackers