Table Lookup to return list of discrete items and specific values

[chowee21]

I have a table that I want to be able to:

1. Create a Data Validation in cell C4 of all of the discrete MODELs listed in column D9:D27. (i.e. A, B,C)
2. Create a Data Validation in cell C5 of all TYPEs from the MODEL selected in C4 (i.e. 1,2,3)
3. Return the DENSITY from the WEIGHT inputted by the user based on the MODEL and TYPE. The table will give a range for the weight

So for example, if I input: Weight=10, Model = C, Size = 2..... it should return Density = 9.90

Prefer to try to only use excel functions, but willing to use VBA if needed.

Book1.xlsx

drive.google.com

 

[Toadstool]

Hi Chowee21,

Please update your profile with your Excel version as you may receive more succinct formulae. My Excel 2016 solution is:

Chowee21.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2										Model	Type
3	WEIGHT	7	<--- input a number							A	1
4	Model	B	<--- create a drop down of all MODELs from table (i.e. A, B,C,etc.)							B	2
5	Type	1	<--- create a drop down of all SIZEs from the MODEL in C5 from table (i.e. 1, 2, 3, etc.)							C	3
6	Density	7.449726	<--- return density of the Model & Size selected based on the weight inputted by the user.							D
7										E
8										F
9			Model	Size	WEIGHT MIN (lb)	WEIGHT MAX (lb)	DENSITY (kg/m2)
10			A	1	0.00	49.99	7.45
11			A	2	50.00	99.99	9.90
12			A	3	100.00	150.00	12.51
13			B	1	0.00	49.99	7.45
14			B	2	50.00	99.99	9.90
15			B	3	100.00	150.00	12.51
16			C	1	0.00	49.99	7.45
17			C	2	50.00	99.99	9.90
18			C	3	100.00	150.00	12.51
19			D	1	0.00	49.99	7.45
20			D	2	50.00	99.99	9.90
21			D	3	100.00	150.00	12.51
22			E	1	0.00	49.99	7.45
23			E	2	50.00	99.99	9.90
24			E	3	100.00	150.00	12.51
25			F	1	0.00	49.99	7.45
26			F	2	50.00	99.99	9.90
27			F	3	100.00	150.00	12.51
Sheet2

Cell Formulas
Range	Formula
K3:K20	K3	=IFERROR(INDEX($D$10:$D$27,MATCH(0,INDEX(COUNTIF($K$2:$K2,$D$10:$D$27),),0))&"","")
L3:L20	L3	=IFERROR(INDEX($E$10:$E$27,AGGREGATE(15,6,ROW($E$10:$E$27)-ROW($E$9)/($D$10:$D$27=$C$4),ROW()-ROW($K$2))),"")
C6	C6	=IF(COUNTIFS($D$10:$D$27,$C$4,$E$10:$E$27,$C$5,$F$10:$F$27,"<="&$C$3,$G$10:$G$27,">="&$C$3)=0,"None",INDEX($H$10:$H$27,MATCH(1,INDEX(($C$4=$D$10:$D$27)*($C$5=$E$10:$E$27),0,1),0)))

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=OFFSET($K$3,,,COUNTIF($K$3:$K$20,"> "))
C5	List	=OFFSET($L$3,,,COUNTIF($L$3:$L$20,">0"))