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?

 

[excelos]

You can if you want...

It gives me a circular reference

 

[Aladin Akyurek]

It gives me a circular reference

What did you try exactly?

 

[excelos]

What did you try exactly?

I replaced all instances of B2:B400 with B:B

 

[Aladin Akyurek]

I replaced all instances of B2:B400 with B:B

The formula is:

=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)),ROWS($O$4:O4))),"")

Try replacing with this one. Recall control+shift+enter.

 

[excelos]

The formula is:

=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)),ROWS($O$4:O4))),"")

Try replacing with this one. Recall control+shift+enter.

Great thanks!

Could you explain me please the bit ROW(UDE!$B$2:$B$400)-ROW(INDEX(UDE!$B$2:$B$400,1,1))+1)) ?
What are we doing there?
Also, why we use ISNUMBER?

Thanks!

 

[excelos]

OOPS there is a problem.

It seems it does not refresh automatically and it shows repetitions.
Also, it should show 200 matches but it continues beyond that when I drag it down.

 

[Aladin Akyurek]

Great thanks!

Could you explain me please the bit ROW(UDE!$B$2:$B$400)-ROW(INDEX(UDE!$B$2:$B$400,1,1))+1)) ?
What are we doing there?
Also, why we use ISNUMBER?

Thanks!

This row expression creates an ascending integer vector that runs from 1 to the size of the range it refers to: 1, 2, …, N where N = size of the reference range.

Note that INDEX(Range,1,1) means the first cell of Range.

ISNUMBER is used to evaluate the results MATCH returns. Recall that MATCH returns either an integer (number) or #N/A. ISNUMBER returns TRUE for integers and FALSE for #N/A's.

 

[Aladin Akyurek]

OOPS there is a problem.

It seems it does not refresh automatically and it shows repetitions.
Also, it should show 200 matches but it continues beyond that when I drag it down.

None of these problems should happen, unless we have an issue here with the whole column references.

 

[excelos]

None of these problems should happen, unless we have an issue here with the whole column references.

The column is 150,000 entries. This can change any time in different files.
So how can I be sure I have the right range if not specifying the whole column?

 

[Aladin Akyurek]

The column is 150,000 entries. This can change any time in different files.
So how can I be sure I have the right range if not specifying the whole column?

What are the exact ranges currently? And which range is numeric if any?