Lookup value from matrix based on 3 search parameters

[HR909]

Hi guys,

i'm having three search values for the combination of which i want to retrieve a valeu from a price matrix. The search values are length, widht and weight. Based on those search values i want to find the row in the price matrix with measurements that do not exceed the lenght, width or weigth of my input variables.

Here the example:

In the example the row 3 in the price matrix is the first row where length, widht and weigth are all higher than my inputs.

What would be the best way to retrieve the price automatically based on my input variables?

I'm stuck with INDEX and MATCH but not getting anywhere with that.

Thanks!

 

[DanteAmor]

Hi and welcome to MrExcel

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Example:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Inputs						Price Matrix
2	Length	Width	Weight	Price			Length	Width	Weight	Price
3	10.50	4.00	2.00	4.00			10.00	4.00	2.00	2.00
4							10.00	4.00	3.00	3.00
5							16.00	6.00	2.00	4.00
6							17.00	7.00	3.00	5.00
Sheet4

Cell Formulas
Range	Formula
D3	D3	=IFERROR(SMALL(IF(($G$3:$G$6>=A3)*($H$3:$H$6>=B3)*($I$3:$I$6>=C3),$J$3:$J$6),1),"None")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[HR909]

Thanks a lot! Works perfectly. Will pay attention to the form to upload sample data.

 

[jdellasala]

Hi and welcome to MrExcel

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Example:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Inputs						Price Matrix
2	Length	Width	Weight	Price			Length	Width	Weight	Price
3	10.50	4.00	2.00	4.00			10.00	4.00	2.00	2.00
4							10.00	4.00	3.00	3.00
5							16.00	6.00	2.00	4.00
6							17.00	7.00	3.00	5.00
Sheet4

Cell Formulas
Range	Formula
D3	D3	=IFERROR(SMALL(IF(($G$3:$G$6>=A3)*($H$3:$H$6>=B3)*($I$3:$I$6>=C3),$J$3:$J$6),1),"None")
Press CTRL+SHIFT+ENTER to enter array formulas.

Great formula!
Another possibility:

=MINIFS($J$3:$J$6,$G$3:$G$6,">="&A4,$H$3:$H$6,">="&B4,$I$3:$I$6,">="&C4)

The new 365 functions make some things so easy (although I'm not crazy about the weird syntax needed for the Criteria here!)

 

[Peter_SSs]

Yet another option.

22 12 19.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Inputs						Price Matrix
2	Length	Width	Weight	Price			Length	Width	Weight	Price
3	10.5	4.0	2.0	4.00			10.0	4.0	2.0	2.00
4	3.0	5.0	2.5	5.00			10.0	4.0	3.0	3.00
5	16.3	7.2	2.0	None			16.0	6.0	2.0	4.00
6							17.0	7.0	3.0	5.00
Price

Cell Formulas
Range	Formula
D3:D5	D3	=INDEX(FILTER(J3:J6,(G3:G6>=A3)*(H3:H6>=B3)*(I3:I6>=C3),"None"),1)

 

[Alex Blakenburg]

Or just a straight XLOOKUP

=XLOOKUP(1,  ($G$3:$G$6>=A3)*($H$3:$H$6>=B3)*($I$3:$I$6>=C3),  $J$3:$J$6, "None")