Return List of values based on drop down list selection

[irvine79]

Looking for a solve returning list of values to cells A3:A?? that correspond to drop down list selection in cell A:1.

Drop down list references 2 column table in columns E & F. Values in column E correspond to validation list in A:1. Looking for formula to return corresponding values from column F.

Toyota				Make	Model
				Ford	F150
Prius				Ford	Taurus
4Runner				Ford	Escort
Land Cruiser				Honda	Civic
Rav4				Honda	Accord
				Toyota	Prius
				Toyota	4Runner
				Toyota	Land Cruiser
				Toyota	Rav4
				Chevy	Malibu
				Chevy	Silverado

<tbody>
</tbody>

 

[WarPigl3t]

Would you like vba code or formula? With formula here is how it would work. Each cell in column G would have a similar formula. Take G1 for example. G1=IF($A$2=E1,F1,"")
I'm assuming that your drop down list is located in A2. So now that you have a list of values in the G column, we need to get rid of the blank cells. As long as you are using at least excel 2010, I can give you an array formula that can do that.

Please tell me where the output should go. For example, you want all of the values coresponding to Prius in column F to go in column B? Also what cell is the drop down list located in?

 

[WarPigl3t]

I'm going to assume your drop down list is located in A1.
I'm going to assume your output starts in A3 and continues down column A.

	A	B	C	D	E	F	G
1	Ford				Make	Model
2					Ford	F150	F150
3	F150				Ford	Taurus	Taurus
4	Taurus				Ford	Escort	Escort
5	Escort				Honda	Civic
6					Honda	Accord
7					Toyota	Prius
8					Toyota	4Runner
9					Toyota	Land Cruiser
10					Toyota	Rav4
11					Chevy	Malibu
12					Chevy	Silverado

<tbody>
</tbody>

Enter this formula into cell G2...

=IF($A$1=E2,F2,"")

Enter this formula into cell A3...

=IFERROR(INDEX(G$2:G$12,AGGREGATE(15,6,(ROW(G$2:G$12)-ROW(G$2)+1)/(G$2:G$12<>""),ROWS(G$2:G2))),"")

You may have to modify this formula to accompany more rows. This formula only accounts for rows G2:G12. Change G12 to your last row or you can even just do something like G99999 as long as you aren't using that column for anything else.

 

[WarPigl3t]

And here is a VBA version.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    DDL = Range("A1").Value
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A3:A" & lastRow).ClearContents
    lastRow = Range("E" & Rows.Count).End(xlUp).Row
    i = 2
    a = 3
    Do Until i > lastRow
        If Range("E" & i).Value = DDL Then
            Range("A" & a).Value = Range("F" & i).Value
            a = a + 1
        End If
        i = i + 1
    Loop
End Sub

 

[irvine79]

Thanks for this, looks like the right track, is there a way of accomplishing without adding the helper column G?

I'm going to assume your drop down list is located in A1.
I'm going to assume your output starts in A3 and continues down column A.

	A	B	C	D	E	F	G
1	Ford				Make	Model
2					Ford	F150	F150
3	F150				Ford	Taurus	Taurus
4	Taurus				Ford	Escort	Escort
5	Escort				Honda	Civic
6					Honda	Accord
7					Toyota	Prius
8					Toyota	4Runner
9					Toyota	Land Cruiser
10					Toyota	Rav4
11					Chevy	Malibu
12					Chevy	Silverado

<tbody>
</tbody>

Enter this formula into cell G2...

=IF($A$1=E2,F2,"")

Enter this formula into cell A3...

=IFERROR(INDEX(G$2:G$12,AGGREGATE(15,6,(ROW(G$2:G$12)-ROW(G$2)+1)/(G$2:G$12<>""),ROWS(G$2:G2))),"")

You may have to modify this formula to accompany more rows. This formula only accounts for rows G2:G12. Change G12 to your last row or you can even just do something like G99999 as long as you aren't using that column for anything else.

 

[WarPigl3t]

No there is not. You can select the entire column G and right click the column header G. Then there will be a dialog menu appearing. Select "hide" from that menu so that you don't have to see the column.

Try out the VBA version. Start by right clicking on the tab called Sheet1 or whatever you may have named it. Then select "view source" or "view code" from the dialog menu that appears. I don't remember which one it say, view source or code. Then copy the vba code I gave you into the window that popped up. You don't need a helper column for this.

 

[Aladin Akyurek]

Looking for a solve returning list of values to cells A3:A?? that correspond to drop down list selection in cell A:1.

Drop down list references 2 column table in columns E & F. Values in column E correspond to validation list in A:1. Looking for formula to return corresponding values from column F.

Toyota				Make	Model
				Ford	F150
Prius				Ford	Taurus
4Runner				Ford	Escort
Land Cruiser				Honda	Civic
Rav4				Honda	Accord
				Toyota	Prius
				Toyota	4Runner
				Toyota	Land Cruiser
				Toyota	Rav4
				Chevy	Malibu
				Chevy	Silverado

<TBODY>
</TBODY>

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($F$2:$F$12,SMALL(IF($E$2:$E$12=$A$1,
  ROW($F$2:$F$12)-ROW($F$2)+1),ROWS($A$3:A3))),"")

 

[irvine79]

Thats the one! Thank you.

A3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($F$2:$F$12,SMALL(IF($E$2:$E$12=$A$1,
  ROW($F$2:$F$12)-ROW($F$2)+1),ROWS($A$3:A3))),"")

 

[Aladin Akyurek]

Thats the one! Thank you.

You are welcome.