Solved: Using vlookup with changing end array column

[grichey]

Let's say I have the following

vlookup($A5,$A$1:$D$10,2,0)

This formula is useless out past column D. Is there a way to make the D in the array dynanmic? I tried using $A$1:column(cellaboveformula)10 but that doesn't work.

I could just make the array that it's looking through out to column BB or some other long ways out there column that I'm sure not to use but I'm wondering if it's possible to do what I'm talking about.

Thanks for the help.

[Bob Phillips]

Try this

=VLOOKUP($A5,OFFSET($A$1,0,0,10,COUNTA($1:$1)),2,FALSE)

[grichey]

Hmm. Isn't that offset just returning 1 cell?

[grichey]

Here's an example file w/ 3 rows. The margin analysis section is the same formula filled down and right. It's unlikely that the rows of the array or analysis are going to change but the columns b/c they're a chronological will.

[Bob Phillips]

No it isn't just returning one cell.

This adaptation of what I gave to suit the actual data is what you need

=VLOOKUP($A11,OFFSET($A$5,0,0,5,COUNTA($1:$1)+1),E$4,FALSE)/E$5

[grichey]

Thanks. It works. I'm going to have mess w/ it to understand it a little better.