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

[DaraMurray]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Phone #[/TD]
[TD]City[/TD]
[TD]Experienced[/TD]
[TD]French[/TD]
[TD]Trained[/TD]
[/TR]
[TR]
[TD]Amanda[/TD]
[TD]123-456-7890[/TD]
[TD]Dallas[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Brenda[/TD]
[TD]234-567-8901[/TD]
[TD]Houston[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Catherine[/TD]
[TD]345-678-9012[/TD]
[TD]Austin[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Debra[/TD]
[TD]567-890-1234[/TD]
[TD]Dallas[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Eve[/TD]
[TD]678-901-2345[/TD]
[TD]Houston[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Frances[/TD]
[TD]890-123-4567[/TD]
[TD]Austin[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[dUBBINS]

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:

[TABLE="width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]

=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()
[/TD]
[/TR]
</tbody>[/TABLE]

 

[T. Valko]

Try this...

Data Range
[Table="class: grid"][tr][td="bgcolor: #c0c0c0"][/td][td="bgcolor: #c0c0c0"]

A​

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

B​

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

C​

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

D​

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

E​

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

F​

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

G​

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

H​

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

I​

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

J​

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

1​

[/td][td]

Name​

[/td][td]

Phone #​

[/td][td]

City​

[/td][td]

Experienced​

[/td][td]

French​

[/td][td]

Trained​

[/td][td]

------​

[/td][td]

City​

[/td][td]

Experienced​

[/td][td]

Name​

[/td][/tr]

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

2​

[/td][td]

Amanda​

[/td][td]

123-456-7890​

[/td][td]

Dallas​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

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

Dallas​

[/td][td]

1​

[/td][td]

Amanda​

[/td][/tr]

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

3​

[/td][td]

Brenda​

[/td][td]

234-567-8901​

[/td][td]

Houston​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

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

Debra​

[/td][/tr]

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

4​

[/td][td]

Catherine​

[/td][td]

345-678-9012​

[/td][td]

Austin​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

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

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

5​

[/td][td]

Debra​

[/td][td]

567-890-1234​

[/td][td]

Dallas​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

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

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

6​

[/td][td]

Eve​

[/td][td]

678-901-2345​

[/td][td]

Houston​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

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

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

7​

[/td][td]

Frances​

[/td][td]

890-123-4567​

[/td][td]

Austin​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

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

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.

 

[DaraMurray]

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.

[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:

[/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!

 

[dfsobral]

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

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

 

[T. Valko]

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.