Verification of a Vehicle price

[NoelD]

I wish to create a price verification excel routine.

I have a price file for a vehicle and a file for the options available for that code.

I can use vlookup to check the base cost of the vehicle, however I am stuck on how to verify the options that are attached to that model code. the option code is universal but price is based on vehicle model code,

any help would be appreciated

 

[RasGhul]

Hi Noel,

Do you have Excel 365? or what version?

 

[RasGhul]

If you have Excel 365, maybe this

Note that (Sheet2!$B$3:$B$10=$E$3),"") returns a blank when there is no match for extras data list (Sheet 2)

Book1
	A	B	C	D	E	F	G	H	I	J
2	MODEL	DESC	MODEL	PRNO	EXTRA DESCRIPTION	DP	DM	CHQ	CHQIN	RRP
3	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	1M6	SWIVEL TRAILER					$801.01
4	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	7K2	PARK DIST					$695.66
5	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	MP1	METALLIC PAINT					$758.59
6	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	MP2	SPECIAL PAINT 1					$883.28
7	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	MP4	SPECIAL PAINT 2					$1,361.30
8	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	MP7	PEARL					$758.59
9	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	MP9	FLAT PAINT					$170.82
10	CB223XI	PASSAT 2.0TDI M6F 150	CB223XI PASSAT 2.0TDI M6F 150	4F2	RANDOM					$777.00
Sheet2

Book1
	B	C	D	E	F	G	H	I	J
2					RRP		DISC	VAT	VRT/NQX
3	MODEL CODE		CB223XI	PASSAT 2.0TDI M6F 150
4
5	EXTRAS		1M6	SWIVEL TRAILER	$801.01
6			4F2	RANDOM	$777.00
7			KA2
8			MP1	METALLIC PAINT	$758.59
Sheet1

Cell Formulas
Range	Formula
E5	E5	=FILTER(Sheet2!$E$3:$E$10,(Sheet2!$D$3:$D$10=D5)*(Sheet2!$B$3:$B$10=$E$3),"")
F5	F5	=FILTER(Sheet2!$J$3:$J$10,(Sheet2!$D$3:$D$10=D5)*(Sheet2!$B$3:$B$10=$E$3),"")

 

[NoelD]

Hi Noel,

Do you have Excel 365? or what version?

Hi RasGhul

I have excel 2019. Thank you for the suggestions

 

[Fluff]

I have excel 2019.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(Sheet1!$K$3:$K$100,AGGREGATE(15,6,(ROW(Sheet1!$K$3:$K$100)-ROW(Sheet1!$K$3)+1)/(Sheet1!$B$3:$B$100=$E$3)/(Sheet1!$D$3:$D$100=D5),1))