Index/Match - Multiple Or Criteria and nth value

[josearistud]

Hello

I need help with the following. I'm using Index / Match to retrieve values from Column C. The criteria o retrieve those values are:

1. Values must have one of these statuses in Column A: Assigned, Working, Waiting, Transferred or Returned.
2. Values must have this status in Column L: Master Ticket

I need also the ability to tell the formula to continue with the second, third, fourth, values and so on. This is my issue here. The formula below retreives the value correctly based on criteria mentioned above, but I can't modify to continue with subsequent values after the first one has been retreieved.

Any help greatly appreciated.

=INDEX('Ticket Tracking Sheet'!$C7:$C20006,MATCH(1,('Ticket Tracking Sheet'!$A7:$A20006="Assigned")*('Ticket Tracking Sheet'!$A7:$A20006="Working")*('Ticket Tracking Sheet'!$A7:$A20006="Waiting")*('Ticket Tracking Sheet'!$A7:$A20006="Transferred")*('Ticket Tracking Sheet'!$A7:$A20006="Returned")+('Ticket Tracking Sheet'!$L7:$L20006="Master Ticket"),0)

 

[RasGhul]

Maybe these options for extracting rows based on 1 & 2, You need to drag down "Extract" to return enough Master Ticket rows or "Extract2" formula spills down since you have Office 365.

Index Match - Multiple Or Criteria and nth value_josearistud.xlsx
	A	B	C									L	M	N	O
6	Status		Values									MasterTicket		Extract	Extract2
7	Assigned		Value1									Master Ticket		Value1	Value1
8	Working		Value2									Master Ticket		Value2	Value2
9	Waiting		Value3											Value4	Value4
10	Transferred		Value4									Master Ticket		Value5	Value5
11	Returned		Value5									Master Ticket		Value7	Value7
12	Assigned		Value6											Value8	Value8
13	Working		Value7									Master Ticket		Value9	Value9
14	Waiting		Value8									Master Ticket		Value10	Value10
15	Transferred		Value9									Master Ticket
16	Returned		Value10									Master Ticket
Ticket Tracking Sheet

Cell Formulas
Range	Formula
O7:O14	O7	=FILTER($C$7:$C$20006,(($A$7:$A$20006="Assigned")+($A$7:$A$20006="Working")+($A$7:$A$20006="Waiting")+($A$7:$A$20006="Transferred")+($A$7:$A$20006="Returned"))*($L$7:$L$20006="Master Ticket"),"")
N7:N16	N7	=IF(ROWS($N$7:N7)>COUNTIF($L$7:$L$20006,"Master Ticket"),"",INDEX($C$7:$C$20006,SMALL(IF((($A$7:$A$20006="Assigned")+($A$7:$A$20006="Working")+($A$7:$A$20006="Waiting")+($A$7:$A$20006="Transferred")+($A$7:$A$20006="Returned"))*($L$7:$L$20006="Master Ticket"),ROW($A$7:$A$20006)-ROW($A$6)),ROWS($N$7:N7))))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[josearistud]

Maybe these options for extracting rows based on 1 & 2, You need to drag down "Extract" to return enough Master Ticket rows or "Extract2" formula spills down since you have Office 365.

Index Match - Multiple Or Criteria and nth value_josearistud.xlsx
	A	B	C									L	M	N	O
6	Status		Values									MasterTicket		Extract	Extract2
7	Assigned		Value1									Master Ticket		Value1	Value1
8	Working		Value2									Master Ticket		Value2	Value2
9	Waiting		Value3											Value4	Value4
10	Transferred		Value4									Master Ticket		Value5	Value5
11	Returned		Value5									Master Ticket		Value7	Value7
12	Assigned		Value6											Value8	Value8
13	Working		Value7									Master Ticket		Value9	Value9
14	Waiting		Value8									Master Ticket		Value10	Value10
15	Transferred		Value9									Master Ticket
16	Returned		Value10									Master Ticket
Ticket Tracking Sheet

Cell Formulas
Range	Formula
O7:O14	O7	=FILTER($C$7:$C$20006,(($A$7:$A$20006="Assigned")+($A$7:$A$20006="Working")+($A$7:$A$20006="Waiting")+($A$7:$A$20006="Transferred")+($A$7:$A$20006="Returned"))*($L$7:$L$20006="Master Ticket"),"")
N7:N16	N7	=IF(ROWS($N$7:N7)>COUNTIF($L$7:$L$20006,"Master Ticket"),"",INDEX($C$7:$C$20006,SMALL(IF((($A$7:$A$20006="Assigned")+($A$7:$A$20006="Working")+($A$7:$A$20006="Waiting")+($A$7:$A$20006="Transferred")+($A$7:$A$20006="Returned"))*($L$7:$L$20006="Master Ticket"),ROW($A$7:$A$20006)-ROW($A$6)),ROWS($N$7:N7))))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Like Magic my friend. Thank you very much!