Formula to get multiple results

mentikk

Board Regular
Joined
Jan 17, 2014
Messages
65
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 :)
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

Board Regular
Joined
Jan 17, 2014
Messages
65

ADVERTISEMENT

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

Board Regular
Joined
Jan 17, 2014
Messages
65
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

Board Regular
Joined
Jan 17, 2014
Messages
65

ADVERTISEMENT

Sorry for multiposting, I had connection problems.
 

mentikk

Board Regular
Joined
Jan 17, 2014
Messages
65
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top