# Match and extract values on different columns

#### glenhmal923

##### New Member
Hi

I am trying to extract from the first column "Name" the matching values from a 2nd Column "City", only if I have at least 3 matching values on column "City".

Basically trying to find all the values in column "Name" that have at least 3 matching values (same values) on column "City".

I have put together an Excel sheet here:

and here is a screenshot of what I mean:

I have tried a few formulas like Index, Match etc but no success, not getting the same output.

Does anyone have any solution or idea on how I can make this work, to get that output?
(check the screenshot, looking to get the names values)

Thank you so much!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### mart37

##### Well-known Member
With a helpcolumn:
Excel question.xlsx
ABCDEF
1NameCityOUTPUT
2EdwardRocklandEdwardRobertEdward
3EdwardCarthageEdwardRaymondRaymond
4EdwardBowieEdwardEdwardRobert
5EdwardPekin
6EdwardInglewood
7RaymondRocklandRaymond
8RaymondCarthageRaymond
9RaymondBowieRaymond
10RaymondDurant
11RaymondDalton
12RobertRocklandRobert
13RobertCarthageRobert
14RobertQuincy
15RobertBowieRobert
16RobertLayton
Sheet1
Cell Formulas
RangeFormula
D2:D16D2=IF(COUNTIF(\$B\$2:\$B\$16,B2)>=3,A2,"")
E2:E16E2=IF(ROW()-1<=SUMPRODUCT(--(FREQUENCY(MATCH(\$D\$2:\$D\$16,\$D\$2:\$D\$16),MATCH(\$D\$2:\$D\$16,\$D\$2:\$D\$16))>0)),INDEX(\$A\$2:\$A\$16,AGGREGATE(14,6,((FREQUENCY(MATCH(\$D\$2:\$D\$16,\$D\$2:\$D\$16),MATCH(\$D\$2:\$D\$16,\$D\$2:\$D\$16))>0)*(ROW(\$D\$2:\$D\$16)-1)),ROW()-1)),"")

#### jasonb75

##### Well-known Member
I haven't looked at this question in detail but there appears to be some similarity to a question that I was looking at last week. My suggestion uses the office 365 dynamic arrays so would not work with excel 2016, but perhaps variations of @Eric W's suggestions could be used here.

#### Fluff

##### MrExcel MVP, Moderator
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Cross posted at: Match values on different columns
and Match and extract values on different columns - OzGrid Free Excel/VBA Help Forum
and Match values on different columns based on criteria

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Last edited:

#### glenhmal923

##### New Member
Thanks a lot guys, I will test the solution!

Replies
2
Views
183
Replies
7
Views
250
Replies
1
Views
189
Replies
0
Views
158
Replies
3
Views
116

1,141,284
Messages
5,705,493
Members
421,398
Latest member
Rahat Anwar

### 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.

### Which adblocker are you using?

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

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