# Lookup value from matrix based on 3 search parameters



## HR909 (Dec 17, 2022)

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 (Dec 17, 2022)

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 AmorABCDEFGHIJ1InputsPrice Matrix2LengthWidthWeightPriceLengthWidthWeightPrice310.504.002.004.0010.004.002.002.00410.004.003.003.00516.006.002.004.00617.007.003.005.00Sheet4Cell FormulasRangeFormulaD3D3=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 (Dec 17, 2022)

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


----------



## jdellasala (Dec 18, 2022)

DanteAmor said:


> 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.
> ...


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 (Dec 18, 2022)

Yet another option.

22 12 19.xlsmABCDEFGHIJ1InputsPrice Matrix2LengthWidthWeightPriceLengthWidthWeightPrice310.54.02.04.0010.04.02.02.0043.05.02.55.0010.04.03.03.00516.37.22.0None16.06.02.04.00617.07.03.05.00PriceCell FormulasRangeFormulaD3:D5D3=INDEX(FILTER(J3:J6,(G3:G6>=A3)*(H3:H6>=B3)*(I3:I6>=C3),"None"),1)


----------



## Alex Blakenburg (Dec 18, 2022)

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")
```


----------

