Index a table of data based on either 1 of 2 critria

[nobbyclarke]

Hi

We use a stop card system to report safety observations. I made a small statistics excel sheet to monitor the progress of closing out those cards.

the cards have three status' open, closed or in progress within the worksheet.

On the same worksheet I want to create a second table that automatically shows the user the cards that are "open" OR in "progress".

I have been using an array formula to work this out...

=IFERROR(INDEX(B$9:B$4003, SMALL(IF($AR$1=$K$9:$K$4003, ROW($K$9:$K$4003)-MIN(ROW($K$9:$K$4003))+1, ""), ROW(A2))), "")

AR1 is the 1st criteria (open)
AR2 not in above formula is the second criteria (in progress)

I have tried to add in the second criteria by the following....

=IFERROR(INDEX(B$9:B$4003, SMALL(IF(OR($AR$1=$K$9:$K$4003,$AR$2=$K$9:$K$4003), ROW($K$9:$K$4003)-MIN(ROW($K$9:$K$4003))+1, ""), ROW(A2))), "")

however this is not working and what I get back is a stop card that is actually closed.

so the question is how can I list all the stop cards that are currently in the "open" or "in progress" state (column K)???

please help.

 

[tommyleinen]

Perhaps a pivot table would do what you need? Assuming the status is in one column and card number in another column.

 

[bosco_yip]

Change this :

=IFERROR(INDEX(B$9:B$4003, SMALL(IF(OR($AR$1=$K$9:$K$4003,$AR$2=$K$9:$K$4003), ROW($K$9:$K$4003)-MIN(ROW($K$9:$K$4003))+1, ""), ROW(A1))), "")

Into this :

=IFERROR(INDEX(B$9:B$4003, SMALL(IF(($AR$1=$K$9:$K$4003)+($AR$2=$K$9:$K$4003), ROW($K$9:$K$4003)-MIN(ROW($K$9:$K$4003))+1, ""), ROW(A1))), "")

Regards

 

[Aladin Akyurek]

Assuming that the first formula instance goes in A2 and copied down...

=IFERROR(INDEX(B$9:B$4003,SMALL(IF(ISNUMBER(MATCH(K$9:$K$4003,$AR$1:$AR$2,0)),ROW($K$9:$K$4003)-ROW($K$9)+1),ROWS($A$2:A2))),"")

If this must be copied across, then replace ROWS with COLUMNS.

 

[nobbyclarke]

Gents - thank you for your help, this worked perfectly thankyou.