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.

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.

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.