Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup formula
I'm trying to do an HLOOKUP or something like it but am having
trouble. Here is a table from my sheet: A B C D E F G 1 Symbol Last Price Stagger % $40 $30 $20 $10 ====== ========== ========= === === === === 2 ASTI $8.30 0.60 3 METH $16.26 4.92% 0.80 4 SOHU $30.08 1.20 0.90 Basically, I've put all the data in but want the formula for Column C. For Column C, we want to take the last price from B and find the next value above it in D1:G1, then look up the corresponding value in D:G on the current row. Then we take what we found and divide it by the last price. I did it for B3 successfully. Here is the formula I used: =HLOOKUP(10-MOD(ROUNDUP($B3,0),10)+ROUNDUP($B3,0),$D$1:$G3,3,F ALSE)/$B3 I don't know if that formula's right or wrong, but the mathematical result is right. That is, $16.26 from B2 is under $20, so we're looking up values in Column F. The value we find is 80 cents in F3. Now we divide that by $16.26 from B3, and we get 4.92%. However, when I drag my formula to C2 or C4 I get error messages. I'm not sure why. The ROUNDUP stuff seems to work, so far as I can see. So the problem's got to be in my HLOOKUP formula. (The dollar signs are just formatting; the numbers are numbers, also in the header column.) There are certainly going to be other approaches to solving this, as well. I'm looking forward to seeing some! Regards, Dallman Ross |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula | Excel Worksheet Functions | |||
Lookup formula help | Excel Worksheet Functions | |||
lookup formula? | Excel Worksheet Functions | |||
Help with Lookup/Sum Formula | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |