Offset And Hlookup Combined

I am trying to combine OFFSET and HLOOKUP but have some probs.

I have a HLOOKUP that returns the correct value..........however instead of pulling that value... I would like to pull the value to the right 1 column.

Example:
=HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)

But on the JAN IND PROD file I want to pull the column to the right of the 34 columns below the referenced A7. Make sense?

I was thinking...

=OFFSET(HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)-1,-2,1,1)

But that does not work.....


THANKS!

Maybe leave off the ))??

You need to use the Index and Match functions instead

Thanks! I will try that...

Originally Posted by duality

You need to use the Index and Match functions instead

Try the following...

=INDEX('JAN IND PROD'!$A$2:$Y$70,34,MATCH($A$7,'JAN IND PROD'!$A$2:$Y$2,0)+1)

Hope this helps!

Originally Posted by gacollege

I am trying to combine OFFSET and HLOOKUP but have some probs.

I have a HLOOKUP that returns the correct value..........however instead of pulling that value... I would like to pull the value to the right 1 column.

Example:
=HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)

But on the JAN IND PROD file I want to pull the column to the right of the 34 columns below the referenced A7. Make sense?

I was thinking...

=OFFSET(HLOOKUP($A$7,'JAN IND PROD'!$A$2:$Y$70,34,FALSE)-1,-2,1,1)

But that does not work.....


THANKS!