To get the Data from multiple Criteria

[chacha123]

Dear all,

Need help. Please refer the attachment.

Thanks


i refer this formula, but can't work


=IFERROR(IF(OR($J$5:$J$13=$I$14),IFERROR(INDEX(I$5:I$13,SMALL(IF($J$5:$J$13=$I$14,ROW($J$5:$J$13)-ROW($J$5)+1),ROWS(I$17:I17))),""),INDEX(I$5:I$13,SMALL(ROW($J$5:$J$13)-ROW($J$5)+1,ROWS(I$17:I17)))),"")

[Glenn Kennedy]

Since you have clearly deleted some columns and rows before posting this, your current formula does not make any sense at all. However, I do know what you are doing. However, life would be much simpler for you if the Branch names in H2 were in separate cells. Is this possible? If not, what is the MAXIMUM number of Branches that cou need to include in the query?

[chacha123]

Since you have clearly deleted some columns and rows before posting this, your current formula does not make any sense at all. However, I do know what you are doing. However, life would be much simpler for you if the Branch names in H2 were in separate cells. Is this possible? If not, what is the MAXIMUM number of Branches that cou need to include in the query?

Yes it is seperate cell and repeated.
branches have only 3

[Glenn Kennedy]

I hope that I have understood you correctly. This array formula will search the data for one name and up to three Branches and will return all matching values. Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

=IFERROR(IFERROR(IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$H$2,ROW($C$2:$C$10))),ROWS($1:1))),INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$I$2,ROW($C$2:$C$10))),ROWS($1:1)-COUNTIFS($A:$A,$H$1,$C:$C,$H$2)))),INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$J$2,ROW($C$2:$C$10))),ROWS($1:1)-(COUNTIFS($A:$A,$H$1,$C:$C,$H$2)+(COUNTIFS($A:$A,$H$1,$C:$C,$I$2)))))),"")

copied across and down.

[chacha123]

I hope that I have understood you correctly. This array formula will search the data for one name and up to three Branches and will return all matching values. Array Formulae are a little different from ordinary formulas in that they MUST be 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 the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

=IFERROR(IFERROR(IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$H$2,ROW($C$2:$C$10))),ROWS($1:1))),INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$I$2,ROW($C$2:$C$10))),ROWS($1:1)-COUNTIFS($A:$A,$H$1,$C:$C,$H$2)))),INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$J$2,ROW($C$2:$C$10))),ROWS($1:1)-(COUNTIFS($A:$A,$H$1,$C:$C,$H$2)+(COUNTIFS($A:$A,$H$1,$C:$C,$I$2)))))),"")

copied across and down.

thanks, it seem like work. let me try apply more..

[Glenn Kennedy]

Apologies. I forgot the attachment. You will be wondering what the hell is going on!!!

[chacha123]

Apologies. I forgot the attachment. You will be wondering what the hell is going on!!!

ok, tried. If let said, i only have 2 branches, Branch A and Branch B

[Glenn Kennedy]

You said that there could be up to 3 Branches - so that is what I provided. If any of the H2 toJ2 cells are left blank, it will work perfectly.

However, if the maximum number of Branches is 2, not 3;then a shorter array formula can be used:

=IFERROR(IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$H$2,ROW($C$2:$C$10))),ROWS($1:1))),INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$I$2,ROW($C$2:$C$10))),ROWS($1:1)-COUNTIFS($A:$A,$H$1,$C:$C,$H$2)))),"")

copied across and down. It will look for a maximum of 2 Branch names in H2 and I2, and a person's name in H1.

[chacha123]

You said that there could be up to 3 Branches - so that is what I provided. If any of the H2 toJ2 cells are left blank, it will work perfectly.

However, if the maximum number of Branches is 2, not 3;then a shorter array formula can be used:

=IFERROR(IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$H$2,ROW($C$2:$C$10))),ROWS($1:1))),INDEX(B:B,SMALL(IF($A$2:$A$10=$H$1,IF($C$2:$C$10=$I$2,ROW($C$2:$C$10))),ROWS($1:1)-COUNTIFS($A:$A,$H$1,$C:$C,$H$2)))),"")

copied across and down. It will look for a maximum of 2 Branch names in H2 and I2, and a person's name in H1.

Oh, Ok. Cause i would like to test if they only 2 Branch Names.
I worried in future there will be less or more.. Branch Names as well, if let said 4 branch name. I need to add more (Index Match), right?

[Glenn Kennedy]

Yes... but it's not quite that simple. You also need to modify the formula to reset the row counter. Do you want me to modify the formula to allow for up to 4 names?

[chacha123]

Yes... but it's not quite that simple. You also need to modify the formula to reset the row counter. Do you want me to modify the formula to allow for up to 4 names?

Yes, please. Thanks

[Glenn Kennedy]

OK. I'm on a train and the wifi isn't great. I will have it back online as soon as i can.

[chacha123]

OK. I'm on a train and the wifi isn't great. I will have it back online as soon as i can.

OK,ok.. sure no problem.. Thank you so much

[Glenn Kennedy]

Finally... the wifi starts to work. Here's the file...

[chacha123]

Finally... the wifi starts to work. Here's the file...

Ok, thanks. Let me try out.

[chacha123]

Thanks Glenn, its work.

[bebo021999]

Glen, while not using this:

IF(ISNUMBER(MATCH($C$2:$C$10,$H$2:$K$2,0)),ROW(INDIRECT("1:"&ROWS($C$2:$C$10))),"")

to create a array of branch match?

then the formula should be more handsome.

[Glenn Kennedy]

Chacha123,
Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

Bebo...
Not sure I follow you - can you explain??

[bebo021999]

This version is for multi branches as criteria, for example, up to 5 branches in H2 to L2 and can be expanded as many as needed.

In G7:

=IFERROR(INDEX(B$2:B$12,SMALL(IFERROR(IF(MATCH($C$2:$C$12,$H$2:$L$2,0)*
($A$2:$A$12=$H$1)>0,ROW(INDIRECT("1:"&ROWS($A$2:$A$12))),""),""),ROWS($1:1))),"")

Confirmed with Ctrl-shift-enter. Drag down and accross.