COMPLICATED FORMULA WITH EMBEDDED HLOOKUP

[txm49]

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.

 

[smartin]

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.

 

[txm49]

This is perfect! Thanks!

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.