Vlookup All Rows based on cell reference

[Maximus2102]

Hoping the experts here can help me. My workbook is set up like the below and my question is at the bottom.

Data Sheet - List of Owner Names, ID's #'s for the car, Type of car, State the car is located in, Color of the car, Qty of cars. (Name is in Column A, CAR ID in Column B etc..)

Name	CAR ID	Car	State	Color	Qty
John S	123	Charger	CA	Black	1
John S	234	S80	CA	Blue	2
John S	567	Accord	CA	Grey	1
Bob A	153	Viper	FL	Blue	2
Bob A	365	Corvette	FL	Black	1
Trey L	785	Challenger	TX	Dark Blue	1
Trey L	698	M5	TX	Grey	2
Chris P	785	Viper	SC	Blue	1
Michael V	487	Charger	WA	Red	1
Michael V	223	Corvette	WA	White	1
Michael V	985	WRX STI	WA	Dark Blue	2
Michael V	923	XJ	WA	Red	1
Michael V	102	S60	WA	Black	1

<tbody>
</tbody>

Names Sheet - List of Owner Names starting in Cell A1 through A6

John S

Bob A

Trey L

Chris P

Michael V

< Select Your Name > Selection Sheet - Drop down box in Cell A1 to select the name Data for the selected name should populate in cells C2:H6 of the Selection Sheet

<tbody>
</tbody>

Need:
On the Selection Sheet the user selects the appropriate name from the drop down box in cell A1 and the data for that name populates in the range of cells (C2:H6).
If there is less than 5 entries then the cells should be left blank.


Notes:
Each name on the Data Sheet will never have more than 5 entries since it will be populated once a week with the top 5 entries for each name.
New customer information can be added to the Data Sheet each week as well as the Names Sheet.


Question:
Is there a formula that will allow me to do this or is there a macro that I can use that will accomplish this?

 

[Obied70]

Each name will have to have a unique value for cells C2:H6 to autopopulate appropriately. Is this possible or will each name have more than one possible result?

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Name	CAR ID	Car	State	Color	Qty
2	John S	123	Charger	CA	Black	1
3	John S	234	S80	CA	Blue	2
4	John S	567	Accord	CA	Grey	1
5	Bob A	153	Viper	FL	Blue	2
6	Bob A	365	Corvette	FL	Black	1
7	Trey L	785	Challenger	TX	Dark Blue	1
8	Trey L	698	M5	TX	Grey	2
9	Chris P	785	Viper	SC	Blue	1
10	Michael V	487	Charger	WA	Red	1
11	Michael V	223	Corvette	WA	White	1
12	Michael V	985	WRX STI	WA	Dark Blue	2
13	Michael V	923	XJ	WA	Red	1
14	Michael V	102	S60	WA	Black	1
Data

Book1
	A	B	C	D	E	F	G	H
1	John S
2	3	Idx	Name	CAR ID	Car	State	Color	Qty
3		1	John S	123	Charger	CA	Black	1
4		2	John S	234	S80	CA	Blue	2
5		3	John S	567	Accord	CA	Grey	1
6
Filter

In A2 just enter:

=COUNTIFS(Data!$A$2:$A$14,A1)

In B3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$2:B2)>$A$2,"",SMALL(IF(Data!$A$2:$A$14=$A$1,ROW(Data!$A$2:$A$14)-ROW(INDEX(Data!$A$2:$A$14,1,1))+1),ROWS($B$2:B2)))

In C3 just enter, copy across, and down:

=IF($B3="","",INDEX(Data!A$2:A$14,$B3))

 

[Obied70]

oh my bad totally misunderstood that all options were to be shown!