ifna index match with if

[blicop]

DataSet:

Factory Code Min Gauge Max Gauge Price CODE1 0.00650 0.00879 1.225 CODE2 0.00800 0.02000 1.215 CODE3 0.00300 0.00390 1.550 CODE4 0.00800 0.00879 1.450 <tbody> </tbody>

<tbody>
</tbody>

Need help with an index-match formula with an if statement that if the gauge entered below is within the min-max of the factory codes matching row above, it should return the appropriate price. Also if it does match the factory code but falls outside the range, it should return "out of range".

Sample Output:

Factory Code	Gauge	Price
CODE3	0.00380	1.550

<tbody>
</tbody>

Any help would be much appreciated

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Factory Code	Min Gauge	Max Gauge	Price		Factory Code	Gauge	Price
2​	CODE1	0.0065​	0.00879​	1.225​		CODE3	0.0038​	1.55​
3​	CODE2	0.008​	0.02​	1.215​
4​	CODE3	0.003​	0.0039​	1.55​
5​	CODE4	0.008​	0.00879​	1.45​

In H2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($D$2:$D$5,MATCH($F2,IF($B$2:$B$5<=$G2,IF($C$2:$C$5>=$G2,$A$2:$A$5)),0)),"not available or out of range")

 

[blicop]

it works! thanks!

 

[Aladin Akyurek]

it works! thanks!

You are welcome.