Returning Value from range array

[gregz12345]

Hello,

I'm looking to inquire about how to return a country's cost, based on a given weight.

The issue is that the weights I'm looking up against have predesignated weight breaks, the given weights ('B' below) may fall between the range.

I'm ultimately looking to return the rounded up value.

EG: weight .332 lbs for UK should return $4.74

Thank you!




A

[TABLE="width: 299"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Weight (lb)[/TD]
[TD]Canada[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD]0.0625[/TD]
[TD]$3.41[/TD]
[TD]$3.21[/TD]
[/TR]
[TR]
[TD]0.1250[/TD]
[TD]$3.64[/TD]
[TD]$3.52[/TD]
[/TR]
[TR]
[TD]0.1875[/TD]
[TD]$3.88[/TD]
[TD]$3.82[/TD]
[/TR]
[TR]
[TD]0.2500[/TD]
[TD]$4.12[/TD]
[TD]$4.13[/TD]
[/TR]
[TR]
[TD]0.3125[/TD]
[TD]$4.36[/TD]
[TD]$4.43[/TD]
[/TR]
[TR]
[TD]0.3750[/TD]
[TD]$4.59[/TD]
[TD]$4.74[/TD]
[/TR]
[TR]
[TD]0.4375[/TD]
[TD]$4.83[/TD]
[TD]$5.04[/TD]
[/TR]
</tbody>[/TABLE]



B

[TABLE="width: 258"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]0.332[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.334[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.349[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.354[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.356[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.359[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.381[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.385[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.397[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.398[/TD]
[TD]United Kingdom[/TD]
[/TR]
[TR]
[TD="align: right"]0.398[/TD]
[TD]United Kingdom[/TD]
[/TR]
</tbody>[/TABLE]
​

 

[steve the fish]

Re: Help Returning Value from range array

With a weight in E1 and country in F1 try:

=INDEX($B$2:$C$8,MATCH(1,INDEX(--($A$2:$A$8>=E1),0),0),MATCH($F1,$B$1:$C$1,0))

 

[gregz12345]

Re: Help Returning Value from range array

With a weight in E1 and country in F1 try:

=INDEX($B$2:$C$8,MATCH(1,INDEX(--($A$2:$A$8>=E1),0),0),MATCH($F1,$B$1:$C$1,0))

Thanks!