Extract data from a list that match multiple criteria

[Kumar Shanmugam]

Hi,

I wanted to extract data from a list that match multiple criteria.

I got 75% solution from Luke's post - http://chandoo.org/wp/2014/11/10/formula-forensics-no-003b-lukes-reward-part-ii/

In Luke's post, there is a formula =IF(COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2),””,INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))))

I customized the formula for my data: =IF(COUNTIFS(NDCC!$A$1:$A$444,Summary!$A$2,NDCC!$B$1:$B$444,Summary!$B$2)<ROWS($A$10:A10)," ",INDEX(NDCC!$C$1:$C$444,LARGE(IF((NDCC!$A$1:$A$444=Summary!$A$2)+(NDCC!$B$1:$B$444=Summary!$B$2)=2,ROW(NDCC!$B$1:B$444)),ROW(NDCC!C1))))

Please refer my sample data:

but criteria is still not completed - I am able to get branches in particular state but i wanted the extracted data to filter in only location having Average buzz greather than state location average and arrear % greater than state arrear.

I am accepting solution like Luke's method.

 

[Hui]

Kumar

You were so very close:
In cell A10: Try:
=IF(COUNTIFS(NDCC!$A$1:$A$444,$A$2,NDCC!$B$1:$B$444,$B$2,NDCC!$E$1:$E$444,">="&$B$5,NDCC!$F$1:$F$444,">="&$C$5)<ROWS($A$10:A10),"",INDEX(NDCC!$C$1:$C$444,LARGE(IF((NDCC!$A$1:$A$444=$A$2)+(NDCC!$B$1:$B$444=$B$2)+(NDCC!$E$1:$E$444>=$B$5)+(NDCC!$F$1:$F$444>=$C$5)=4,ROW(NDCC!$B$1:B$444)),ROW(NDCC!C1)))) Ctrl+Shift+Enter

Copy down
Copy across and adjust

I added the Red parts above and changed the Blue parts

or see the attached file:

 

[Kumar Shanmugam]

Thank you very much. Formula works perfectly after adjustment.

I also want to sort the data in descending order as per arrear% column. What should be the code for that. How can I add Rank () function in this formula.

Also I have a difficulty in understanding the logic : Formula Forensics No. 003b – Lukes Reward – Part II. Please find the attached file.

Formula: =IF(COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2),"",INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))))

My question is why not ROW($B$2:$B$25)),ROW(B1) or ROW($A$2:$A$25)),ROW(A1)

 

[Hui]

The Column part doesn't matter which column, its just a counter

have you read the first part of that post at:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/

I would first sort the source data, I wouldn't try and do it in one formula.

 

[Kumar Shanmugam]

Hi Help required, I applied above complex formula. Now my file is not working, It has become corrupt.

I have send the file to my colleague. Please find the error.

 

[Kumar Shanmugam]

Also I am getting below error when I am trying to open the file,

 

[Hui]

I suspect there is something wrong with your Installation of Excel
I have never seen a formula corrupt an Excel file,
Formula can cause Excel to crash, but to corrupt a file is normally either an Excel Installation or Memory issue

Here is the repaired file
There are no formula's inside it

What are you trying to achieve?