How to Pull ALL matching values from a list/array

[SirGruffles]

Hello,

I have a list of account numbers in the format of "XXXX-YYY-ZZZ- -" in column A, specifically A6-A684.

Is there a function in excel, similar to Vlookup, that would pull out any and all account numbers that match a certain criteria?

For instance, ideally, I would want to pull out any and all account numbers that end in "100-100- -", and I figured that (including the extra dashes), would require the use of Right(data,10) as the matching criteria. Or something with an If statement to have that match the 100-100- -? Blargh, brain hurt.

But after that, I get completely stumped as to how I can make it scroll through the entire list and give me ALL the matching accounts, and not simply the first one it finds in the list.

Thank you very much for any and all help on this.

(I'm at a new job and would like to look knowledgeable >.>)

Thanks!

 

[mikerickson]

Would filtering get what you want?
If you need this sub-set of your data to be in a different location, you could look at Advanced Filter and its Copy To Other Location option.
Otherwise, VBA would be needed.

 

[SirGruffles]

Yeah, its a bit more manual than I was hoping for.
As to get the reports to my superiors quicker, I did the manual filtering and made 35 different tabs for the cost centers.

I just thought there may have been a formula or something more automated that would have made the process quicker and more efficient.
I thought something like Match or Index? could have helped?
No idea.

I just recently got pivot tables down, so these crazier shenanigans have me brain dead.

 

[Marcelo Branco]

See if this example helps


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Account​

[/td][td][/td][td]

Criteria​

[/td][td]

List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0001-100-100- -​

[/td][td][/td][td]

100-100- -​

[/td][td]

0001-100-100- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

0002-100-101- -​

[/td][td][/td][td][/td][td]

0003-100-100- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

0003-100-100- -​

[/td][td][/td][td][/td][td]

0004-100-100- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

0004-100-100- -​

[/td][td][/td][td][/td][td]

0006-100-100- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

0005-100-102- -​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

0006-100-100- -​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in C2

Array formula in D2 copied down
=IFERROR(INDEX(A$2:A$7,SMALL(IF(RIGHT(A$2:A$7,10)=C$2,ROW(A$2:A$7)-ROW(A$2)+1),ROWS(D$2:D2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Adjust the ranges to suit

M.

 

[SirGruffles]

Yes! This!
This was what I was looking for!

Thank you very much!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.

 

[SirGruffles]

Out of curiosity, if there was a requirement of 2 criteria, for a single column, would there be a way to insert both criteria into that one formula?
For instance, this one report would be for centers ending in 110-200 and 110-250.

 

[Marcelo Branco]

Out of curiosity, if there was a requirement of 2 criteria, for a single column, would there be a way to insert both criteria into that one formula?
For instance, this one report would be for centers ending in 110-200 and 110-250.

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Account​

[/td][td][/td][td]

CriteriaList​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

0001-100-100- -​

[/td][td][/td][td]

110-200- -​

[/td][td]

0002-110-200- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

0002-110-200- -​

[/td][td][/td][td]

110-250- -​

[/td][td]

0004-110-250- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

0003-100-100- -​

[/td][td][/td][td][/td][td]

0005-110-200- -​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

0004-110-250- -​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

0005-110-200- -​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

0006-100-100- -​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in D2 copied down
=IFERROR(INDEX(A$2:A$7,SMALL(IF(ISNUMBER(MATCH(RIGHT(A$2:A$7,10),C$2:C$3,0)),ROW(A$2:A$7)-ROW(A$2)+1),ROWS(D$2:D2))),"")
Ctrl+Shift+Enter

M.