Problem with LOOKUP of last non empty cell and OFFSET/INDEX across two worksheets

[urbnri]

Hi all,

I have a workbook with two sheets, Sheet1 (the summary) Sheet2 (the data).


On the summary sheet, I'm using this formula to return the last non empty cell in a column on sheet 2

I'm using this: =LOOKUP(2,1 / (Sheet2!A1:A10 <> ""),Sheet2!A1:A10) - lets call result of this value X

I find X this way because a new number is added every week, below the last entry.



Okay, now to the problem!

On the summary sheet I would also like to show a value (value Y)which is always two cells to the right of X on sheet 2.

As the sheet is updated weekly, X and Y will be one row down each week (hence using the formula to find X).

I can't use the same formula to find Y as the column which Y is in is fully populated and never needs updated like X does.



What formula can I use, which will take X as a reference, and show Y?

I've tried a combination of OFFSET, INDEX, and VLOOKUPS to no avail - any ideas?





Thanks!!!

 

[XOR LX]

On the summary sheet I would also like to show a value (value Y)which is always two cells to the right of X on sheet 2.

So can't you just use:

=LOOKUP(2,1/(Sheet2!A1:A10<>""),Sheet2!C1:C10)

?

Regards

 

[urbnri]

Hey thank you XOR LX,

Unfortunately I can't use that formula as I'm not trying to find the last non empty cell in that column (as all the cells in that column are already filled.)

So the column where I'm looking for X - I'm always looking for the last non empty cell as there is new data added weekly, this parts okay.

However the column where I'm looking for Y - I'm not looking for the last non empty cell, as the column is already filled with data, there is nothing new added each week.

What I'm trying to do is always find Y in relation to X, I know X will always be the last value in the fist column, and Y will always be adjacent to X - but not the last value in its column (column 2).


e.g.
Week 1 column1 has 10 filled rows, column2 has 100 filled rows.
Week 2 column1 has 11 filled rows, column2 has 100 filled rows.
Week 3 column1 has 13 filled rows, column2 has 100 filled rows.

So I know how to find the X in column1, using a similar formula to the one you posted.
But I can't find the matching Y in column two as it isn't the last number, its the number adjacent to the most recent X in column 1.

So in week3, X will be in column1 row 13, and Y will be in row 13 column2.

I hope this makes sense!

Thanks

 

[XOR LX]

Did you actually try the formula I posted?

=LOOKUP(2,1 / (Sheet2!A1:A10 <> ""),Sheet2!A1:A10)

=LOOKUP(2,1/(Sheet2!A1:A10<>""),Sheet2!C1:C10)

My formula is looking in precisely the same column as yours. It's just that it's returning from a different one.

Regards

 

[urbnri]

Thanks, let me try again I could have made an error. When I tried your formula it returned X value again but I could have made a mistake. Cheers!