Get last column with data within an HLOOKUP

[espenskeie]

Hi

I have this code that I want to only look within a dynamic range.

=HLOOKUP(I5;Comments!D3:G11;3)

D3:G11 should be D3:"Last column With data in row 3"

I5 is a cell where the user can Select week number, i Guess this function doesn't make any problems to the formula...

Any suggestions to how I solve this?

Br
Espen

 

[jorismoerings]

Hi,

Replace your FORMULA with this: =HLOOKUP(I5;OFFSET(COMMENTS!D3;0;0;4;MAX(COLUMN(D:AA)*(D3:AA3<>""))-3);3;0)
and confirm with Ctrl+Shift+enter as it's an array formula.
If your data goes beyond column AA change that accordingly in the MAX function.

 

[espenskeie]

Thanks

I now have another Challenge. As the data grows, I want to create an array of last four columns only, like a rolling period.

So if I have data from D3 to O11, I would the area to be L3:O11, do you know how I can modify to get this to work?

Br
Espen

 

[jorismoerings]

Hi,

You probably know different questions needs to be in different threads but ....
=OFFSET(D3;0;MAX(COLUMN(D:AA)*(D3:AA3<>""))-4;9;-4)

Again confirmed with Ctrl+Shift+Enter

This creates a range from L3:O11

 

[espenskeie]

Thanks a lot. Yes i was walking in the shade of the forum rules...

I did bump into a New problem, and will take that to a New post

Thanks
Espen