ifna index match with if

[blicop]

DataSet:
[TABLE="width: 341"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 341"]
<tbody>[TR]
[TD]Factory Code[/TD]
[TD]Min Gauge[/TD]
[TD]Max Gauge[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]CODE1[/TD]
[TD="align: right"]0.00650[/TD]
[TD="align: right"]0.00879[/TD]
[TD="align: right"]1.225[/TD]
[/TR]
[TR]
[TD]CODE2[/TD]
[TD="align: right"]0.00800[/TD]
[TD="align: right"]0.02000[/TD]
[TD="align: right"]1.215[/TD]
[/TR]
[TR]
[TD]CODE3[/TD]
[TD="align: right"]0.00300[/TD]
[TD="align: right"]0.00390[/TD]
[TD="align: right"]1.550[/TD]
[/TR]
[TR]
[TD]CODE4[/TD]
[TD="align: right"]0.00800[/TD]
[TD="align: right"]0.00879[/TD]
[TD="align: right"]1.450[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 287"]
<tbody>[TR]
[TD]Factory Code[/TD]
[TD]Gauge[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]CODE3[/TD]
[TD="align: right"]0.00380[/TD]
[TD="align: right"]1.550[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be much appreciated

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td]Factory Code[/td][td]Min Gauge[/td][td]Max Gauge[/td][td]Price[/td][td][/td][td]Factory Code[/td][td]Gauge[/td][td]Price[/td][/tr]
[tr][td]

2​

[/td][td]CODE1[/td][td]

0.0065​

[/td][td]

0.00879​

[/td][td]

1.225​

[/td][td][/td][td]CODE3[/td][td]

0.0038​

[/td][td]

1.55​

[/td][/tr]
[tr][td]

3​

[/td][td]CODE2[/td][td]

0.008​

[/td][td]

0.02​

[/td][td]

1.215​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]CODE3[/td][td]

0.003​

[/td][td]

0.0039​

[/td][td]

1.55​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]CODE4[/td][td]

0.008​

[/td][td]

0.00879​

[/td][td]

1.45​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.