Index Match with multiple criteria

[TheSubject]

Hi all,

I hope someone can help me with a problem I'm trying to get around with multiple criteria of an Index/Match function.

Column A is a list of clothes
Hat
Sock
Mask
etc.

Column B is a list of body parts:
Head
Chest
etc.


I now need unique lists in columns D-F headed with the body part, ie Column D:
HEAD
Hat
Mask

I can make a formula that gives the dynamic and unique list of all the clothes, OR I can make the formula that gives a dynamic list of the right clothes - except it just says Hat Hat Hat....

This is what I have currently:

{=INDEX($A$2:$A$248,MATCH(0,(COUNTIF(D$1:D1,$A$2:$A$248)*(NOT($D$1=$B$2:$B$248))),0))}

Can anyone help?

Many thanks!

G

 

[jarjarbingie]

Hi all,

I hope someone can help me with a problem I'm trying to get around with multiple criteria of an Index/Match function.

Column A is a list of clothes
Hat
Sock
Mask
etc.

Column B is a list of body parts:
Head
Chest
etc.


I now need unique lists in columns D-F headed with the body part, ie Column D:
HEAD
Hat
Mask

I can make a formula that gives the dynamic and unique list of all the clothes, OR I can make the formula that gives a dynamic list of the right clothes - except it just says Hat Hat Hat....

This is what I have currently:

{=INDEX($A$2:$A$248,MATCH(0,(COUNTIF(D$1:D1,$A$2:$A$248)*(NOT($D$1=$B$2:$B$248))),0))}

Can anyone help?

Many thanks!

G

D2 = IFERROR(INDEX($A$2:$A$248,SMALL(IF($B$2:$B$248=D$1,ROW($B$2:$B$248),""),ROW()-ROW($D$1))-1),"") press CTRL+SHIFT+ENTER

where D1=Head

 

[TheSubject]

D2 = IFERROR(INDEX($A$2:$A$248,SMALL(IF($B$2:$B$248=D$1,ROW($B$2:$B$248),""),ROW()-ROW($D$1))-1),"") press CTRL+SHIFT+ENTER

where D1=Head

Perfect! Loving how people actually take the time to respond on this forum! That was exactly what I needed and I never would have worked it out myself, but can see now how it works, so will use it in the future!

Thanks a lot

 

[Varbaro]

D2 = IFERROR(INDEX($A$2:$A$248,SMALL(IF($B$2:$B$248=D$1,ROW($B$2:$B$248),""),ROW()-ROW($D$1))-1),"") press CTRL+SHIFT+ENTER

where D1=Head

Dear all

sorry for awakening this old topic, but i have a similar problem, my only addition to this formula that i want one more criteria to create my list.
So in this part:
SMALL(IF($B$2:$B$248=D$1
from this formula:
D2 = IFERROR(INDEX($A$2:$A$248,SMALL(IF($B$2:$B$248=D$1,ROW($B$2:$B$248),""),ROW()-ROW($D$1))-1),"")

i want to implement another criteria for example, IF($E$2:$E$248=xy)
how to add 1 or more criteria in this?
If i make after the IF an AND(the two criteria) it returns a #VALUE.

Thanks in advance!

 

[Fluff]

How about
=FILTER(A2:A248,(B2:B248=D1)*(E2:E248="xy"),"No data")

 

[Varbaro]

Hello, i want to make a list with {} sequence if i pull the function down, so i need this IFERROR(INDEX($A$2:$A$248,SMALL(IF($B$2:$B$248=D$1,ROW($B$2:$B$248),""),ROW()-ROW($D$1))-1),"") base setup to remain. (with implementing with CTRL+SHIFT+ENTER as well)

 

[Fluff]

Did you actually try my idea?

 

[Varbaro]

Did you actually try my idea?

Hello, yes. Filter is not a valid function.

 

[Fluff]

Are you on the semi annual channel?