hLookup dynamic range

B

Bongard

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.

=OFFSET(CELL("Address",INDEX(Data!$CA$2:$DD$2,MATCH("PE",Data!$CA$2:$DD
$2,0))),0,0,COUNTA(CELL("Address",INDEX(Data!$CA$2:$DD$2,MATCH
("PE",Data!$CA$2:$DD$2,0)))),1)

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!
Brian
 
B

Bongard

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

Similar Threads

Dynamic Range for multiple columns 7
Dynamic Range Using INDEX instead of Offset 4
Dynmic Range in pivot table 1
Dynamically Naming a Table of Data 2
Dynamic named range reference from external workbook 2
#value! error trying to create a simple dynamic named range 1
ignore formulas in dynamic range 1
automatically update pivot tables when dynamic named range is expa 10

Top