Lookup and return

[mthead]

I have the following formula in a spreadsheet:

=IF(AND(Sheet1!$I$13>=FreightFactors!$B$3:$B$102,Sheet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFactors!$A$3:$A$102,"")

I13 contains a formula that calculates a weight.

Freight Factors Column A contains a Number. FreightFactors Column B contains
a hard coded Low range. FreightFactors Column C contains a hard coded High
range. For instance:

A1 = 0001 B1 = 0 C1 = 2.9
A2 = 0002 B2 = 3 C2 = 5.9
A3 = 0003 B3 = 6 C3 = 8.9

Etc. The High Low range keeps going up to 250.

So, the first time I plugged in all my values on Sheet1 and the weight was
calculated in cell I13, the formula returned the value of FreightFactors
Column A perfectly. It found the row where the range fell between the value
of Columns B and C, and returned the value of Column A from that same row. I
then copied the formula to another cell (I need this done in about 12
different cells on Sheet!), made sure all the cell references were still Ok,
but I didn't work. It just returned the Value_if_False, which in this case
is nothing. Stranger still, if I changed some of my initial values and the
value of I13 changed, then the original formula returned the Value_if_False.

Any ideas why this is happening, or does someone have a better way of doing
it?

Thanks.

 

[JLatham]

It seems to me that you could use SUMPRODUCT() here:
=SUMPRODUCT(--(FreightFactors!$B$3:$B$102<Sheet1!$I$13),
--(FreightFactors!$C$3:$C$102>Sheet1!$I$13), (FreightFactors!$A$3:A$102))
That will return a 0 instead of "" when a value in Sheet1!I13 doesn't fall
between 2 values in B:C.
Also, that formula excludes the values in B and C; i.e., if I13 contained
2.9, it would be a 0 return, if you need to include the values in B:C for the
tests, then
=SUMPRODUCT(--(FreightFactors!$B$3:$B$102<=Sheet1!$I$13),
--(FreightFactors!$C$3:$C$102>=Sheet1!$I$13), (FreightFactors!$A$3:A$102))

Finally, if you want a "" instead of 0 for 'not in range' results, then
=IF(SUMPRODUCT(--(FreightFactors!$B$3:$B$102<=Sheet1!$I$13),
--(FreightFactors!$C$3:$C$102>=Sheet1!$I$13), (FreightFactors!$A$3:A$102))=0,
"", SUMPRODUCT(--(FreightFactors!$B$3:$B$102<=Sheet1!$I$13),
--(FreightFactors!$C$3:$C$102>=Sheet1!$I$13), (FreightFactors!$A$3:A$102)))

 

[mthead]

Thank you! That worked perfectly. You truly are an MVP!