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!
 

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
503
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
Joined
May 9, 2009
Messages
16,623
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
Joined
Feb 6, 2017
Messages
14
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
Joined
Oct 19, 2015
Messages
130
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
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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.
 

Forum statistics

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

Some videos you may like

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