Create a list based on multiple "or" criteria

[jkg77]

I'm trying to create a single list of serial numbers associated with multiple customer account IDs.

Example: Using the data set below, I need a formula that produces a single list of serial numbers associated with ALL of the following account IDs: ABC, GHI and STU.

The formula should produce the following output in cell D2:D7...
001
002
005
006
013
014

[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]Account ID (cell A1)[/TD]
[TD]Serial Number (cell B1)[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]002[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]003[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD]004[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]005[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD]006[/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD]007[/TD]
[/TR]
[TR]
[TD]JKL[/TD]
[TD]008[/TD]
[/TR]
[TR]
[TD]MNO[/TD]
[TD]009[/TD]
[/TR]
[TR]
[TD]MNO[/TD]
[TD]010[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD]011[/TD]
[/TR]
[TR]
[TD]PQR[/TD]
[TD]012[/TD]
[/TR]
[TR]
[TD]STU[/TD]
[TD]013[/TD]
[/TR]
[TR]
[TD]STU[/TD]
[TD]014[/TD]
[/TR]
[TR]
[TD]VWX[/TD]
[TD]015[/TD]
[/TR]
[TR]
[TD]VWX[/TD]
[TD]016[/TD]
[/TR]
</tbody>[/TABLE]




























I have the following formula but it only gets me a list based on any ONE account ID. I have not been able to find one that works for multiple account IDs.
{=IFERROR(INDEX($B$2:$B$25|SMALL(IF($A$2:$A$25="ABC"|ROW($B$2:$B$25)-ROW($B$2)+1)|ROWS(D$2:D2)))|"")}

Thank you!

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Account ID (cell A1)[/td][td]Serial Number (cell B1)[/td][td][/td][td][/td][td]

1​

[/td][/tr]

[tr][td]

2​

[/td][td]ABC[/td][td]1[/td][td][/td][td][/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]ABC[/td][td]2[/td][td][/td][td][/td][td]

5​

[/td][/tr]

[tr][td]

4​

[/td][td]DEF[/td][td]3[/td][td][/td][td][/td][td]

6​

[/td][/tr]

[tr][td]

5​

[/td][td]DEF[/td][td]4[/td][td][/td][td][/td][td]

13​

[/td][/tr]

[tr][td]

6​

[/td][td]GHI[/td][td]5[/td][td][/td][td][/td][td]

14​

[/td][/tr]

[tr][td]

7​

[/td][td]GHI[/td][td]6[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]JKL[/td][td]7[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]JKL[/td][td]8[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]MNO[/td][td]9[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]MNO[/td][td]10[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]PQR[/td][td]11[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]PQR[/td][td]12[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]STU[/td][td]13[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]STU[/td][td]14[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

16​

[/td][td]VWX[/td][td]15[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

17​

[/td][td]VWX[/td][td]16[/td][td][/td][td][/td][td][/td][/tr]
[/table]
E1=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$17={"ABC","GHI","STU"},ROW($A$2:$A$17)),ROWS($A$1:A1))),"")
ARRAY entered using CTRL SHIFT ENTER, not just enter
Then copy down as needed

 

[jkg77]

Works perfectly! If I wanted to use cell references instead of {"ABC","GHI","STU"}, how would that work? Let's assume my cell references are G2, G3 and G4 respectively.