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:

Closed	Customer	Country	Revenue	Status
1-7-2013	A	USA	234	Lost
1-6-2014	B	UK	3465	Won
20-10-2014	C	Ger	856	Won
11-12-2014	C	Ger	345	Lost
3-3-2015	A	USA	6786	Won

<tbody>
</tbody>

 

[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