Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with find the last number(s) in a tabel

Posted on 2013-12-11
Medium Priority
213 Views
Folks I have a table ranged K3:P7 with numerous numbers.
I also have a table K11:L15 when K11:K15 is numbered 3,4,5,6,7 to correspond to the row numbers.
In L11:L15 I want to place the last values from each row in my K3:P7 table using the array function.What I have below, which is about to 20th version is no better than version one.
``````=INDEX(11:11,MAX(ISNUMBER(11:11)*COLUMN(11:11)))
``````
I'm really confused on the INDEX part here also.
0
Question by:Frank Freese
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2
• 2

LVL 11

Expert Comment

ID: 39712900
Can you post the Excel?
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39712921
In L11 use this formula copied down to L15

=LOOKUP(9.99E+307,K3:P3)

That will get you the last number in each row

regards, barry
0

LVL 11

Expert Comment

ID: 39712927
Better than mine! Thanks for the tip Barry :)
0

Author Comment

ID: 39712929
barry,
how you came up with this....anyway, IT WORKED!
thanks
0

Author Closing Comment

ID: 39712931
thanks to the both of you
good job barry
0

Author Comment

ID: 39712951
Barry,
could you briefy explain to me what this formula is saying?
0

LVL 50

Expert Comment

ID: 39712991
9.99E+307 is the biggest number you can input in a cell in Excel (equivalent to 9.99 with 305 zeroes) so when you lookup using that number as a lookup value it probably won't be found.

If LOOKUP can't find the lookup value then it matches with the last number in the lookup range (because that should be the largest in a sorted range), so it does what you want here, finds the last number.

For last text value

=LOOKUP(REPT("z",255),range)

or for last value, number or text

=LOOKUP(2,1/(range<>""),range)

regards, barry
0

Author Comment

ID: 39713003
thanks barry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month9 days, 13 hours left to enroll