Help!!!!

[euan_walker]

I have a large spreadsheet of information, with an extract as per below.

	A	B	C	D	E	F	G
1	Make	Model	Manufactured	Purchased	Fuel	Value	Status
2	Ford	Focus	2013	2013	Petrol	Not for Sale	Ford
3	Nissan	Note	2013	2015	Petrol	For Sale	Nissan
4	Nissan	Micra	2014	2014	Petrol	For Sale	Nissan
5	Ford	Mondeo	2012	2013	Diesel	For Sale	Ford
6	Ford	Mondeo	2011	2013	Petrol	For Sale	Ford
7	Ford	Fiesta	2015	2014	Petrol	Not for Sale	Ford
8	Kia	Sportage	2014	2015	Diesel	Sold	Kia
9	Fiat	500L	2014	2014	Petrol	Not for Sale	Fiat
10	Audi	A3	2013	2013	Petrol	Not for Sale	Audi
11	Audi	A3	2015	2016	Petrol	Not for Sale	Audi
12	Kia	Sportage	2015	2015	Diesel	For Sale	Kia
13	Ford	Focus	2014	2015	Diesel	Not for Sale	Ford
14	Honda	Accord	2015	2015	Diesel	Sold	Honda

<tbody>
</tbody>

Is there a solution within excel that will allow me to take certain area of this information from one work sheet and create a continuous list in another worksheet of the information without distorting the original spreadsheet and not creating gaps in the new worksheet.

In worksheet 2, I am looking to create a drop down list in cell B2 and C2 then within Cells A2:D5 it would bring back information from sheet 1 dependant on my selection as per below.

	A	B	C	D
1	Selection	Ford	Status	Not For Sale
2	Make	Model	Fuel	Status
3	Ford	Focus	Petrol	Not for Sale
4	Ford	Fiesta	Petrol	Not for Sale
5	Ford	Focus	Diesel	Not for Sale

<tbody>
</tbody>

I would assume that a VLOOKUP would be the answer however I think that there is multiple makes the same this keeps returning an error

Any assistance would be greatly appreciated

Many Thanks

 

[jessi81882]

What about using a Pivot Table? Search on this site for how to set up a dynamic range on which to base the pivot table

 

[63falcondude]

Are filters not acceptable?

 

[AliGW]

If neither pivot tables nor filters are acceptable, try this:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L
1	Make	Model	Manufactured	Purchased	Fuel	Status			Selection	Ford	Status	Not For Sale
2	Ford	Focus	2013	2013	Petrol	Not for Sale			Make	Model	Fuel	Status
3	Nissan	Note	2013	2015	Petrol	For Sale			Ford	Focus	Petrol	Not for Sale
4	Nissan	Micra	2014	2014	Petrol	For Sale			Ford	Fiesta	Petrol	Not for Sale
5	Ford	Mondeo	2012	2013	Diesel	For Sale			Ford	Focus	Diesel	Not for Sale
6	Ford	Mondeo	2011	2013	Petrol	For Sale
7	Ford	Fiesta	2015	2014	Petrol	Not for Sale
8	Kia	Sportage	2014	2015	Diesel	Sold
9	Fiat	500L	2014	2014	Petrol	Not for Sale
10	Audi	A3	2013	2013	Petrol	Not for Sale
11	Audi	A3	2015	2016	Petrol	Not for Sale
12	Kia	Sportage	2015	2015	Diesel	For Sale
13	Ford	Focus	2014	2015	Diesel	Not for Sale
14	Honda	Accord	2015	2015	Diesel	Sold
Sheet4

Cell Formulas
Range	Formula
I3	{=IFERROR(INDEX($A$1:$A$50,SMALL(IF(($A$2:$A$50=$J$1)*($F$2:$F$50=$L$1),ROW($A$2:$A$50)),ROW(A1))),"")}
J3	{=IFERROR(INDEX($B$1:$B$50,SMALL(IF(($A$2:$A$50=$J$1)*($F$2:$F$50=$L$1),ROW($A$2:$A$50)),ROW(B1))),"")}
K3	{=IFERROR(INDEX($E$1:$E$50,SMALL(IF(($A$2:$A$50=$J$1)*($F$2:$F$50=$L$1),ROW($A$2:$A$50)),ROW(C1))),"")}
L3	{=IFERROR(INDEX($F$1:$F$50,SMALL(IF(($A$2:$A$50=$J$1)*($F$2:$F$50=$L$1),ROW($A$2:$A$50)),ROW(D1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.