Offset with hlookup as reference

[DoubleZ]

In excel 2007 I basically want to use the offset function to return a long
list. However, I need the returned list to be based upon a certain input
value. So I tried to use offset with hlookup as the reference value, but it
didn't work. I now understand why since hlookup returns a value, not a
reference.

Is there a way to return a reference cell rather than that cell's value?

Thanks.

 

[David Biddulph]

MATCH?

 

[DoubleZ]

I think that MATCH returns a value, not a reference cell.

 

[David Biddulph]

Tells you how far along the array the value has been found.

 

[DoubleZ]

Thanks David. I understand now.

For anyone reading this post trying to figure out the same thing I was, here
is my solution:

Originally I was using offset with hlookup as the reference. Instead, I
used offset, using the first cell as my reference and using the match
function to define how many columns to offset from the reference(e.g.
=offset(A1,2,Match(Sheet2!B4,Sheet1!A1:Z1,0)-1,409,2), where Sheet1!A1:Z1 was
the range containing values I wanted to look through, and Sheet2!B4 was the
cell I wanted to match.

Hope that helps.