Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COMPLICATED FORMULA WITH EMBEDDED HLOOKUP
OBJECTIVE: Use HLOOKUP to return datapoint. Then use a formula in a
separate cell to return datapoint in first column to right. Sample Data Array: Col A Col B Row 1 Date Row 2 Plan Forecast Row 3 10 9 Row 4 11 8 Sample HLookup Formula: HLOOKUP(Ref,A1:b4,3,false) Returns 10 (Plan Value), NEED TO RETURN 9 (Forecast Value) in separate formula. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COMPLICATED FORMULA WITH EMBEDDED HLOOKUP
txm49 wrote:
OBJECTIVE: Use HLOOKUP to return datapoint. Then use a formula in a separate cell to return datapoint in first column to right. Sample Data Array: Col A Col B Row 1 Date Row 2 Plan Forecast Row 3 10 9 Row 4 11 8 Sample HLookup Formula: HLOOKUP(Ref,A1:b4,3,false) Returns 10 (Plan Value), NEED TO RETURN 9 (Forecast Value) in separate formula. Can't tell from your example how the nontrivial cases will be presented. Assuming you have other blocks of information in columns to the right, maybe this will do it for you: Plan: =INDEX($A$3:$K$3,1,MATCH("Date",$A$1:$K$1,0)) Forecast: =INDEX($A$3:$K$3,1,1+MATCH("Date",$A$1:$K$1,0)) Replace "Date" with reference as appropriate. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COMPLICATED FORMULA WITH EMBEDDED HLOOKUP
This is perfect! Thanks!
"smartin" wrote: txm49 wrote: OBJECTIVE: Use HLOOKUP to return datapoint. Then use a formula in a separate cell to return datapoint in first column to right. Sample Data Array: Col A Col B Row 1 Date Row 2 Plan Forecast Row 3 10 9 Row 4 11 8 Sample HLookup Formula: HLOOKUP(Ref,A1:b4,3,false) Returns 10 (Plan Value), NEED TO RETURN 9 (Forecast Value) in separate formula. Can't tell from your example how the nontrivial cases will be presented. Assuming you have other blocks of information in columns to the right, maybe this will do it for you: Plan: =INDEX($A$3:$K$3,1,MATCH("Date",$A$1:$K$1,0)) Forecast: =INDEX($A$3:$K$3,1,1+MATCH("Date",$A$1:$K$1,0)) Replace "Date" with reference as appropriate. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with complicated formula | Excel Worksheet Functions | |||
Complicated Formula | Excel Discussion (Misc queries) | |||
Complicated Formula | Excel Worksheet Functions | |||
Need help doing complicated formula | Excel Worksheet Functions | |||
I have a complicated formula that I need SERIOUS help with please! | Excel Discussion (Misc queries) |