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

#### DaraMurray

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

 Name Phone # City Experienced French Trained Amanda 123-456-7890 Dallas 1 0 0 Brenda 234-567-8901 Houston 0 1 0 Catherine 345-678-9012 Austin 0 0 1 Debra 567-890-1234 Dallas 1 1 1 Eve 678-901-2345 Houston 1 1 1 Frances 890-123-4567 Austin 1 1 1

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

#### dUBBINS

##### Well-known Member
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:

#### T. Valko

##### Well-known Member
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.

#### DaraMurray

##### New Member
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!

#### dfsobral

##### Board Regular
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

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

##### Well-known Member
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.

1,081,708
Messages
5,360,781
Members
400,595
Latest member
T_Dubs

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...