Help with unfamiliar lookup()

[Salamanders]

Hi Everyone,

First post! I have inherited a spreadsheet that from a retiring analyst. This formula returns the last number in the column. I just don't know how it works. I have not seen the lookup used this way before. Can anyone help me with this (see below)?

=LOOKUP(2,1/('Sheet 1'!U:U<>""),'Sheet 1'!U:U)

Thanks in advance!

[Jonmo1]

'Sheet 1'!U:U<>"" is an expression, or a question, and it has a True or False answer.
So it goes through the range U:U, and asks the question at each cell
<>"" is basically 'Not blank'
Is U1 Not Blank
Is U2 Not Blank
Is U3 Not Blank
etc..

So you end up with an array containing all the true or false answers to that question.
=LOOKUP(2,1/({True,False,False,True,True,False,etc}),'Sheet 1'!U:U)

Each True and False is then divided into 1.
So you have an array of divisions
=LOOKUP(2,{1/True,1/False,1/False,1/True,1/True,1/False,etc},'Sheet 1'!U:U)

When applying a math operation like / divisition, then True = 1 and False = 0
=LOOKUP(2,{1/1, 1/0, 1/0, 1/1, 1/1, 1/0},'Sheet 1'!U:U)

1/1 = 1
1/0 = #Div/0! error
=LOOKUP(2,{1, #Div/0!, #Div/0!, 1, 1, #Div/0!},'Sheet 1'!U:U)


Now a nice feature of Lookup is that it ignores error values like #Div/0!
And since the lookup value (2) is larger than all numeric values in the lookup range
Lookup then results in the last (furthest to the right) numeric value in the lookup range.


Hope that helps.

[Salamanders]

'Sheet 1'!U:U<>"" is an expression, or a question, and it has a True or False answer.
So it goes through the range U:U, and asks the question at each cell
<>"" is basically 'Not blank'
Is U1 Not Blank
Is U2 Not Blank
Is U3 Not Blank
etc..

So you end up with an array containing all the true or false answers to that question.
=LOOKUP(2,1/({True,False,False,True,True,False,etc}),'Sheet 1'!U:U)

Each True and False is then divided into 1.
So you have an array of divisions
=LOOKUP(2,{1/True,1/False,1/False,1/True,1/True,1/False,etc},'Sheet 1'!U:U)

When applying a math operation like / divisition, then True = 1 and False = 0
=LOOKUP(2,{1/1, 1/0, 1/0, 1/1, 1/1, 1/0},'Sheet 1'!U:U)

1/1 = 1
1/0 = #Div/0! error
=LOOKUP(2,{1, #Div/0!, #Div/0!, 1, 1, #Div/0!},'Sheet 1'!U:U)


Now a nice feature of Lookup is that it ignores error values like #Div/0!
And since the lookup value (2) is larger than all numeric values in the lookup range
Lookup then results in the last (furthest to the right) numeric value in the lookup range.


Hope that helps.

Wow! Thank you Jonmo1! This makes a lot more sense now. It really does help!

[Jonmo1]

Now, all that said.

Here is a far more efficient formula to find the last number in a range

=LOOKUP(9.99999999999999E+307,'Sheet 1'!U:U)

[Jonmo1]

You're welcome.

[leelnich]

Here's a corollary to @Jonmo1 's post #3 formula. This one finds the last TEXT in the range:

=LOOKUP(REPT("z",255),'Sheet 1'!U:U)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee