index small if - multiple criteria

[orsm6]

Hi all,

Messing around with a formula and struggling to make it work. it is an array formula, currently it works with one condition, but i do not know how to get it working with multiple conditions.

the formula:

 =IFERROR(INDEX('All Orders'!$A$1:$A$500,SMALL(IF('All Orders'!$D$1:D$500="GMP",ROW('All Orders'!$A$1:$A$500),""),ROW('All Orders'!A1))),"")

i am trying to add add where GMP exists into the formula: GRP and GNP
on another sheet i need to add: ZZZ, PTS, PQF, PQT, PSS and PSW

TIA for any assistance

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX('All Orders'!$A$1:$A$500,SMALL(IF(ISNUMBER(MATCH('All Orders'!$D$1:D$500,List,0)),ROW('All Orders'!$A$1:$A$500)-ROW(INDEX('All Orders'!$A$1:$A$500,1,1))+1),ROWS($1:1))),"")

where List a vertical range housing items like GMP, GRP, and GNP, etc.

 

[orsm6]

Hi Aladin, thank you for the help. I have tried this formula. it returns a blank cell.
formula is on sheet named Nova in cell A2

regarding "List" is this a range containing the items to be matched that is separate from the range being looked up?

also unclear on this section of the formula: ,ROWS($1:1))),"") it is looking up a blank row on the sheet that i have the formula in.

 

[orsm6]

never mind that.... List is a named range containing the GNP and GRP items etc. I believe this is working now. thank you very much for this help.

 

[Aladin Akyurek]

never mind that.... List is a named range containing the GNP and GRP items etc. I believe this is working now. thank you very much for this help.

You are welcome.