Non Array Based 4 Criteria Combination Data Extraction

[pop]

I am trying to adapt a sheet http://www.youtube.com/watch?v=SiaTyBcvj8I for an xcelsius dashboard so that it can extract data from a list based on any combination of 4 criteria (ie extract data using any single criteri, or any combination of 2 criteria or any combination of 3 criteria amd lastly extract matches for all 4 criteria. I have tried using a lookup column that identifies the number of matches and their positions cannot seem to construct the formula correctly to achieve this.


Lookup Column Formulae for 2 crtieria


=AND(B3=$G$3,C3=$H$3)+A2


The forumulae to extract the data


=IF($L3>$H$5,"",INDEX(B$1:B$85,MATCH($L3,$A$1:$A$85,0),1))


The spreadsheet drives an Xcelsius Dashboard and so cannot use any array formulae and a number of other functions are prohibited.


Any insights will be greatly appreciated.


Lookup Col


0 Ethnicity Gender Grade Status

0 Unknown M K 1

0 Unknown M K 1

0 Unknown M K 0

0 Unknown M K 0

1 Unknown F K 0

2 Unknown F K 0

2 Unknown M K 0

2 Unknown M K 0

3 Unknown F K 0


Extraction Criteria


1 1 0 0 No of Criteria

Ethnicity Gender Grade Status

Unknown f 2


Count 28


Extracted Data


Ethnicity Gender Grade Status

1 Unknown F K 0

2 Unknown F K 0

3 Unknown F K 0

4 Unknown F K 0

 

[Hui]

Pop


Firstly, Welcome to the Chandoo.org Forums


You could use an Advanced Filter to do what you want

Refer: http://chandoo.org/wp/2011/10/10/how-to-use-advanced-filters/

 

[pop]

hey hui


thanks for your reply.


I really need to try and achieve the outcome of the advanced filter with a formula that I can use with Xcelsius. I really need a non array based formula for a lookup column that will allow firstly to determine how many criteria (between 1-4 and any combination) has been selected and then to perform or mark the records for extraction or to be able to be looked up.