hLookup dynamic range



Hi, I am having trouble figuring out how to make a range dynamic both
horizontally and vertically in my sheet. I am using offset formula in
the format =OFFSET(Data!$CL$1,0,0,COUNTA(Data!$CL:$CL),1) to make the
range dynamic as more rows of data are added but I also need the
column to be dynamic. For instance I need this range (PriceToEarnings)
to always reference the column from the 'data' sheet that contains
"PE" in row 3. I have tried the following formula with no luck.


Basically I want to search for "PE" count the columns in that row and
have that data be my range for analysis. The column that contains "PE"
may change and the amount of rows may change which is why I'm trying
to make this dynamic in two directions (if that makes sense).

Thanks a lot to anyone that can help!


Sorry Unfortunately I have Excel 2003 at my workplace and there's no
way around that.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question