Return a list of matches

E

excelos

Well-known Member
Joined
Sep 25, 2011
Messages
592
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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?
 
Upvote 0
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?
 
Upvote 0
[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.
 
Upvote 0
Aladin Akyurek said:
[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.
Click to expand...

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?
 
Upvote 0
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));"")​
 
Last edited:
Upvote 0
Aladin Akyurek said:
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));"")​
Click to expand...

Thanks, but I don't want it to work only on UDE!$B$2:$B$400, but on UDE!$B:$B
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
A search formula (like index match) that can pull the most recent date when multiple matches may occur based on the criteria figure.
Replies
2
Views
152
MARZIOTULLIO
M
M
  • Question Question
Matching Times
Replies
5
Views
166
Moti
M
B
  • Solved
return value on match of list?
Replies
1
Views
72
MARZIOTULLIO
M
A
  • Solved
FIND when leading zeros have been added to a number, or not! ...
Replies
1
Views
223
dreid1011
dreid1011
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
298
henrik2h
H

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top