Help with searching a multiple column spreadsheet for closest matching rows

[Compuzed]

Help - I am just starting down the road of using complex formulas and am a serious newbie - but this one has me hooped.
I have a 4 column spreadsheet ( Year, DCPD, Make, and Model).
What I need to do is be able to enter the Year, Make and Model ( built from a pull down list of the values within each column) and then find the closes resultant value for the DCPD column - if that makes sense. I do have the spreadsheet but I dont want to post the whole thing, but I could if it is needed. I think the explanation is quite clear, but I could be wrong... ? Thanks in advance !

 

[BSALV]

do you have excel 2021 or 365 ?
A small example with the XL2BB-tool is a great help.

 

[Compuzed]

do you have excel 2021 or 365 ?
A small example with the XL2BB-tool is a great help.

I do not have the permissions necessary to install that tool; although it would simplify the explanation. Here is a sample of the data I have to work with. I would like to be able to setup a query on this sheet, asking for year, make and model, and display its corresponding data from the DCPD column. If no match is exactly found ( which will happen with newer cars), then it would return the DCPD valued from the next lowest years value for the same make and model. Hope that makes sense?

 

[BSALV]

and your excelversion is 2021 or 365 or older ?

 

[Fluff]

@Compuzed What version of Excel are you using?

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’)

 

[BSALV]

compuzed.xlsx

compuzed.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	1	2	3	4	5	6	7	8	9	10	11	12	13
2
3	year	row	collision		comp		DCPD		AB		Make		Model						year	Make	Model	DCPD	collision	comp	row
4	2016	4	98		15		82				toyota		model_2			make	toyota		2016	toyota	model_2	92	37	60	45
5	2010	5	72		98		82				toyota		model_1			model	Model_2		2016	toyota	model_2	82	98	15	4
6	2016	6	64		14		21				toyota		model_3			year	2016		2016	toyota	model_2	73	96	56	70
7	2002	7	34		56		62				toyota		model_3
8	2015	8	86		33		59				toyota		model_2						The 3 best 'DCPD'-cars of make toyota, model Model_2, Year 2016
9	2002	9	52		15		37				toyota		model_3
10	2012	10	98		18		67				toyota		model_1
11	2012	11	78		25		60				toyota		model_3
12	2002	12	69		63		39				toyota		model_1
Blad1

Cell Formulas
Range	Formula
A1:M1	A1	=COLUMN()
S4:Y6	S4	=IFERROR(INDEX(SORT(FILTER(MyData,(MyMakes=make)*(MyModels=model)*(MyYears=year)),7,-1),SEQUENCE(3),TRANSPOSE({1;11;13;7;3;5;2})),"-")
S8	S8	="The 3 best 'DCPD'-cars of make " &make & ", model " & model & ", Year " &year
B4:B12	B4	=ROW()
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
make	=Blad1!$Q$4	S8, S4
model	=Blad1!$Q$5	S8, S4
year	=Blad1!$Q$6	S8, S4

 

[Compuzed]

sorry, guess I was not clear. In 3 data entry spots, I want to enter the Year, Make and Model. The formula will then return the nearest DCPD value based on the resultant lookup. Does that explain it better.
For instance, I just did one where I needed to find the value of a 2018 Chev Impala... and I need to find the DCPD value for that corresponding row, or the next closest to it.

 

[BSALV]

same link

compuzed.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	5	6	7	8	9	10	11	12	13	14
2
3	comp		DCPD		AB		Make		Model	∆|DCPD|					year	Make	Model	DCPD	collision	comp	row
4	15		82				toyota		model_2	32		make	toyota		2016	toyota	model_2	47	85	81	64
5	98		82				toyota		model_1	32		model	Model_2		2016	toyota	model_2	73	96	56	70
6	14		21				toyota		model_3	29		year	2016		2016	toyota	model_2	82	98	15	4
7	56		62				toyota		model_3	12		DCPD	50		2016	toyota	model_2	92	37	60	45
8	33		59				toyota		model_2	9					-	-	-	-	-	-	-
9	15		37				toyota		model_3	13
10	18		67				toyota		model_1	17
Blad1

Cell Formulas
Range	Formula
E1:M1	E1	=COLUMN()
S4:Y8	S4	=IFERROR(INDEX(SORT(FILTER(MyData,(MyMakes=make)*(MyModels=model)*(MyYears=year)),14,1),SEQUENCE(5),TRANSPOSE({1;11;13;7;3;5;2})),"-")
N4:N10	N4	=ABS(G4-$Q$7)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
make	=Blad1!$Q$4	S4
model	=Blad1!$Q$5	S4
year	=Blad1!$Q$6	S4