::: Excel - How to find the last number of a column and its related data ?

[infojacques]

Hello,

Is there any formula that gives you the last number of a column ?
Based on that number can I found the data related to it (from the same
line) ?
My questions are detailed into this Excel file : http://cjoint.com/?ewkEgKqJeI

Thanks for your help.
Jacques

 

[Dave Peterson]

=LOOKUP(10^99,A:A)
or
=LOOKUP(9.99999999E+307,A:A)

Use any number that you know will be bigger that the biggest number in your
data.

9.99999999E+307 is the largest number you can put into a cell. But 10^99 is
easier to type.

 

[Ed Ferrero]

Hi Jacques,

To find the last number in column A. Assuming numbers are greater
than -99999;

row no =MATCH(-99999,A:A,-1)
value =INDEX(A:A,MATCH(-99999,A:A,-1))
Address =ADDRESS(MATCH(-99999,A:A,-1),1)
Value in Column B =OFFSET($A$1,MATCH(-99999,A:A,-1)-1,1)

Ed Ferrero
www.edferrero.com

 

[Max]

Some thoughts .. implemented in your sample:
http://cjoint.com/?ewoLP08fEH

In Summary,

Array-entered** in C3:
=INDEX(OFFSET(Data!$A$12:$A$30,,),
MAX(IF(ISBLANK(OFFSET(Data!$A$12:$A$30,,MATCH($D3,Data!$11:$11,0))),0,ROW($A$1:$A$19))))
C3 copied down to C7

Array-entered** in E3:
=INDEX(OFFSET(Data!$A$12:$A$30,,MATCH($D3,Data!$11:$11,0)+COLUMN(A1)-1),
MAX(IF(ISBLANK(OFFSET(Data!$A$12:$A$30,,MATCH($D3,Data!$11:$11,0))),0,ROW($A$1:$A$19))))
E3 copied across to H3, filled down to H7

**Press CTRL+SHIFT+ENTER to confirm the formula, instead of just pressing
ENTER
The formula will appear wrapped by curly braces: { } within the formula bar
if correctly array-entered

 

[infojacques]

Dear All,
Thank you very much for your answers.
I will go through them and try to understand them.
Have a great day !
Jacques

 

[Max]

Welcome, Jacques !