Return a list of matches

[excelos]

Hello

In a file with several columns one of which is the ID column, I want to populate a list of IDs that are specific matches (eg COUNTIF matches).

The problem is that the matches can be 0,1,2,3 etc.

How can I do that?

 

[djmixer135]

Hi,

Have you tried using the Advanced filter in excel? This will generate a new list of results depending on a criteria you determine.

Can you show an example of what you are trying to achieve?

 

[Aladin Akyurek]

Care to post a very small sample along with the desired results?

 

[excelos]

It's simple:

A;B;C;D
3;5;6;1
6;2;4;6
2;6;7;8

I want to return the list of A that have C= 6 or 4
So I want the list of A=3 and A=6

But this list in different files, may be 0 entries or 5 or 100.
How do I return this dynamic list?

I was wondering if I can use INDEX/MATCH and somehow return the first, second, third instance so that my vertical list will be populated by the entries and will go as down as needed each time.

But I don't think that is possible, any other solution?

 

[Aladin Akyurek]

[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]c[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]list[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In G5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$D$4;SMALL(IF(ISNUMBER(MATCH(INDEX($A$2:$D$4;0;MATCH($G$1;$A$1:$D$1;0));$G$2:$G$3;0));ROW($A$2:$D$4)-ROW(INDEX($A$2:$D$4;1;1))+1);ROWS($1:1));MATCH($G$1;$A$1:$D$1;0));"")

Note. Replace the semi-colon with comma if you are on an American system.

 

[excelos]

[TABLE="class: grid, width: 448"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]c[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]list[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In G5 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$D$4;SMALL(IF(ISNUMBER(MATCH(INDEX($A$2:$D$4;0;MATCH($G$1;$A$1:$D$1;0));$G$2:$G$3;0));ROW($A$2:$D$4)-ROW(INDEX($A$2:$D$4;1;1))+1);ROWS($1:1));MATCH($G$1;$A$1:$D$1;0));"")

Note. Replace the semi-colon with comma if you are on an American system.

Thanks, I tried to make it a bit simpler but it doesn't work:
{=INDEX(UDE!$B:$B,1,SMALL(IF(AND(UDE!$AC:$AC="Awaiting",OR(UDE!$A:$A="Hubs",UDE!$A:$A="Branches")),ROW(UDE!$B:$B)-MIN(ROW(UDE!$B:$B))+1),$O$4:O4))}
Any help please?

 

[Aladin Akyurek]

Try not to make simpler, try as is.

 

[Aladin Akyurek]

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

=IFERROR(INDEX(UDE!$B$2:$B$400;SMALL(IF(UDE!$AC$2:$AC$400="Awaiting";IF(ISNUMBER(MATCH(UDE!$A$2:$A$400;{"Hubs";"Branches"};0));ROW(UDE!$B$2:$B$400)-ROW(INDEX(UDE!$B$2:$B$400;1;1))+1));$O$4:O4));"")​

 

[excelos]

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

=IFERROR(INDEX(UDE!$B$2:$B$400;SMALL(IF(UDE!$AC$2:$AC$400="Awaiting";IF(ISNUMBER(MATCH(UDE!$A$2:$A$400;{"Hubs";"Branches"};0));ROW(UDE!$B$2:$B$400)-ROW(INDEX(UDE!$B$2:$B$400;1;1))+1));$O$4:O4));"")​

Thanks, but I don't want it to work only on UDE!$B$2:$B$400, but on UDE!$B:$B

 

[Aladin Akyurek]

Thanks, but I don't want it to work only on UDE!$B$2:$B$400, but on UDE!$B:$B

You can if you want...