Populate cell with info from one column based on criteria from two other columns

DaraMurray

New Member
Joined
Feb 6, 2017
Messages
14
Hi there!

I am new to posting on the forum, but have been an avid reader as I work on my Excel skills (after switching from another platform).

Here's my question that is beyond my skillset:

I have a large list that I need to pull subsets from. My master list contains names, phone numbers, addresses, etc. I want to create smaller lists in brand new sheets by pulling a name, for example, only if certain criteria are met such as: city, experience, etc. I've hit a frustration wall.

NamePhone #CityExperiencedFrenchTrained
Amanda123-456-7890Dallas100
Brenda234-567-8901Houston010
Catherine345-678-9012Austin001
Debra567-890-1234Dallas111
Eve678-901-2345Houston111
Frances890-123-4567Austin111

<tbody>
</tbody>

In my NEW sheet (Sheet2) I would want to create a formula so that a name would be populated based on living in Dallas and being "Experienced" so that my first column in my new sheet is only filled with the names of experienced people who also live in Dallas. I would then go on to populate the next column in the new sheet with phone numbers of those people, but once I have the names I can just do VLOOKUP or INDEX/MATCH. It's the first step I'm hung up on!

I tried something like this: =INDEX('Sheet1'!A2:F7,,1,IF('Sheet1'!C2:C7,"Dallas",AND('Sheet1'!D2:D7,"0")))

Which of course returned an error.

Thank you in advance for your time and help!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Welcome, I try to give back, as I also learn a lot from these forums. This was a formula I just learned from another member, and it actually fits perfect for what you are asking. I made a few slight changes to fit your example.

This will pull the names based on experience being 1:

Code:
=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-ROW($A$2)+1/($D$2:$D$7=1),ROWS($D$2:D2))),"")

And yes, then just use a VLOOKUP()


<tbody>
</tbody>
 
Last edited:
Upvote 0
Try this...

Data Range
A
B
C
D
E
F
G
H
I
J
1
Name​
Phone #​
City​
Experienced​
French​
Trained​
------​
City​
Experienced​
Name​
2
Amanda​
123-456-7890​
Dallas​
1​
0​
0​
Dallas​
1​
Amanda​
3
Brenda​
234-567-8901​
Houston​
0​
1​
0​
Debra​
4
Catherine​
345-678-9012​
Austin​
0​
0​
1​
5
Debra​
567-890-1234​
Dallas​
1​
1​
1​
6
Eve​
678-901-2345​
Houston​
1​
1​
1​
7
Frances​
890-123-4567​
Austin​
1​
1​
1​

This array formula** entered in J2:

=IFERROR(INDEX(A:A,SMALL(IF((C$2:C$7=H$2)*(D$2:D$7=I$2),ROW(A$2:A$7)),ROWS(J$2:J2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.
 
Upvote 0
Thank you both for your replies, I really appreciate it. I tried both but neither seemed to work perfectly in my actual data set (I am double checking against my real data by doing a simple =IF((AND(C2="Dallas",O2=1)),TRUE,FALSE) to populate a column I can sort to check names.

Code:
[COLOR=#333333]=IFERROR(INDEX(A:A,SMALL(IF((C$2:C$7=H$2)*(D$2:D$7=I$2),ROW(A$2:A$7)),ROWS(J$2:J2))),"")
-- this formula pulled names (yay!), but only pulled them in the exact order they appear in the spreadsheet. [/COLOR]

This is what I am currently using in my sheet:

Code:
[/COLOR]=IFERROR(INDEX('Sheet1'!A2:A5515,SMALL(IF(('Sheet1'!$N$2:$N$5515="Hollywood")*('Sheet1'!$O$2:$O$5515="0"),ROW('Sheet1'!A2)),ROWS('Sheet1'!N2))),"")[COLOR=#333333]

Thanks again for your time and help!
 
Upvote 0
Hi

If VBA is an option for you this code will work perfectly, assuming I understand what you need

First select the entire table (headlines included) and after run this macro

Code:
Sub GetInfo()
Dim myCity As String
Dim myExperienced As String
Dim sArray As Variant
Dim dLine As Long
Dim i As Long, j As Long
 
myCity = InputBox("Enter City to search")
myExperienced = InputBox("Enter experience to search")
 
sArray = Selection
Worksheets.Add , Sheets(Sheets.Count)
 
dLine = 1
For j = 1 To UBound(sArray, 2)
    Cells(1, j) = sArray(1, j)
Next j
 
For i = 2 To UBound(sArray, 1)
    If sArray(i, 3) = myCity And sArray(i, 4) = myExperienced Then
        dLine = dLine + 1
        For j = 1 To UBound(sArray, 2)
            Cells(dLine, j) = sArray(i, j)
        Next j
    End If
Next i
 
End Sub

This macro will automatically add a new sheet with all the information you need
 
Upvote 0
This is what I am currently using in my sheet:

=IFERROR(INDEX('Sheet1'!A2:A5515,SMALL(IF(('Sheet1'!$N$2:$N$5515="Hollywood")*('Sheet1'!$O$2:$O$5515="0"),ROW('Sheet1'!A2)),ROWS('Sheet1'!N2))),"")

Try it like this...

=IFERROR(INDEX('Sheet1'!A:A,SMALL(IF(('Sheet1'!$N$2:$N$5515="Hollywood")*('Sheet1'!$O$2:$O$5515=0),ROW('Sheet1'!A$2:A$5515)),ROWS('Sheet1'!N$2:N2))),"")

Still array entered.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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