Formula to get multiple results

[mentikk]

Hi,
I'm looking for help to get a formula to receive all matching results. So far I get just the first one.
In worksheet "IWKA" I need to get list of values in column Zlecenie (column A). As you can see I have formula in cell A3 and do not know how to get next results for A4, A5 etc as far as there are matching results.
Values should be from worksheet "zrzut" and the criteria is that it gets all values with IWKA in column I (WC).
The thing is that data in worksheet are about to be updated/changed every day nad there may be different amount of those - sometimes 1000, sometimes 1500.
If there is an option I would also like to find out how to add another criteria - take a column C (status) and get only values between 20 and 40.
http://asset-b.soup.io/asset/6619/7177_b5e5.xlsx
Thank you for any help

 

[Marcelo Branco]

Maybe this

1st question

Array formula in A3 copied down
=IF(COUNTIF(zrzut!$I:$I,$D$1)>=ROWS(A$3:A3),INDEX(zrzut!$A:$I,SMALL(IFERROR(IF(zrzut!$I$2:$I$2000=$D$1,ROW(zrzut!$I$2:$I$2000)),""),ROWS(A$3:A3)),1),"")

confirmed withCtrl+Shift+Enter, not just Enter


2nd question (Status between 20 and 40)

Array formula in A3 copied down
=IF(COUNTIFS(zrzut!$I:$I,$D$1,zrzut!$C:$C,">=20",zrzut!$C:$C,"<=40")>=ROWS(A$3:A3),INDEX(zrzut!$A:$I,SMALL(IFERROR(IF(zrzut!$I$2:$I$2000=$D$1,IF(zrzut!$C$2:$C$2000>=20,IF(zrzut!$C$2:$C$2000<=40,ROW(zrzut!$I$2:$I$2000)))),""),ROWS(A$3:A3)),1),"")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[mentikk]

You are my god!
Works perfectly, thank you a lot.

 

[Marcelo Branco]

You are my god!
Works perfectly, thank you a lot.

You are welcome and thanks for the feedback.

M.

 

[mentikk]

Sadly option doesn't work. I'm not good enough to find out why so I'd like to ask for help with it.

 

[mentikk]

Sadly option doesn't work. I'm not good enough to find out why so I'd like to ask for help with it.

 

[mentikk]

Sorry for multiposting, I had connection problems.

 

[mentikk]

Sorry for all those problems with my posts.
The thing is that option 1 is working great, but option 2 is not. If I use option 2 formula i get only empty cells as results. Can anyone see a way to solve it?
Thanks

 

[Marcelo Branco]

Sorry for all those problems with my posts.
The thing is that option 1 is working great, but option 2 is not. If I use option 2 formula i get only empty cells as results. Can anyone see a way to solve it?
Thanks

There are no registers that meet the conditions: Column I = D1 (IWKA) and Status between 20 and 40.

That's why you are getting only empty cells as results with the second formula.


M.

 

[Marcelo Branco]

Sorry for all those problems with my posts.
The thing is that option 1 is working great, but option 2 is not. If I use option 2 formula i get only empty cells as results. Can anyone see a way to solve it?
Thanks

There are no registers that meet the conditions: Column I = D1 (IWKA) and Status between 20 and 40.

That's why you are getting only empty cells as results with the second formula.

M.