List of values from table using multiple criteria

[borolo222]

Hi I need to use the example in this page

link

but with a twist...

Instead of using one lookup value criteria I need to use two or more..

   A    B     C         
 1 item value state     
 2 1    a     pending 
 3 2    b     ready     
 4 3    c     pending 
 5 1    d     ready     
 6 2    e     pending 
 7 1    f     ready
 8 2    g     pending 
 9 3    h     ready
10 1    i     pending 
11 2    j     ready
12 1    k     pending

So I want to display a list of values for example all items "1" with state "pending", resulting in

a
i
k

thks for your help,

Emilio

 

[VoG]

Try a Pivot Table.

 

[Aladin Akyurek]

Hi I need to use the example in this page

link

but with a twist...

Instead of using one lookup value criteria I need to use two or more..

   A    B     C         
 1 item value state     
 2 1    a     pending 
 3 2    b     ready     
 4 3    c     pending 
 5 1    d     ready     
 6 2    e     pending 
 7 1    f     ready
 8 2    g     pending 
 9 3    h     ready
10 1    i     pending 
11 2    j     ready
12 1    k     pending

So I want to display a list of values for example all items "1" with state "pending", resulting in

a
i
k

thks for your help,

Emilio

E2: 1

which is an item of interest.

E3: Pending

which is a state of interest.

E4, just enter:

=SUMPRODUCT(--(A2:A12=E2),--(C2:C12=E3))

E5: List

E6, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$6:E6)<=$E$4,INDEX($B$2:$B$12,
    SMALL(IF($A$2:$A$12=$E$2,IF($C$2:$C$12=$E$3,
      ROW($B$2:$B$12)-ROW($B$2)+1)),ROWS($E$6:E6))),"")

 

[borolo222]

E2: 1

which is an item of interest.

E3: Pending

which is a state of interest.

E4, just enter:

=SUMPRODUCT(--(A2:A12=E2),--(C2:C12=E3))

E5: List

E6, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$6:E6)<=$E$4,INDEX($B$2:$B$12,
    SMALL(IF($A$2:$A$12=$E$2,IF($C$2:$C$12=$E$3,
      ROW($B$2:$B$12)-ROW($B$2)+1)),ROWS($E$6:E6))),"")

Wonderful!!!

Thanks very much.

 

[Aladin Akyurek]

Wonderful!!!

Thanks very much.

You are welcome. Thanks for providing feedback.