Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and return
I have the following formula in a spreadsheet:
=IF(AND(Sheet1!$I$13=FreightFactors!$B$3:$B$102,S heet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFa ctors!$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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and return
It seems to me that you could use SUMPRODUCT() he
=SUMPRODUCT(--(FreightFactors!$B$3:$B$102<Sheet1!$I$13), --(FreightFactors!$C$3:$C$102Sheet1!$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" wrote: I have the following formula in a spreadsheet: =IF(AND(Sheet1!$I$13=FreightFactors!$B$3:$B$102,S heet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFa ctors!$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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and return
Thank you! That worked perfectly. You truly are an MVP!
"JLatham" wrote: It seems to me that you could use SUMPRODUCT() he =SUMPRODUCT(--(FreightFactors!$B$3:$B$102<Sheet1!$I$13), --(FreightFactors!$C$3:$C$102Sheet1!$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" wrote: I have the following formula in a spreadsheet: =IF(AND(Sheet1!$I$13=FreightFactors!$B$3:$B$102,S heet1!$I$13<=FreightFactors!$C$3:$C$102),FreightFa ctors!$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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and return value | Excel Worksheet Functions | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
Lookup and return | Excel Worksheet Functions | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Lookup from first row, return last row value | Excel Programming |