Questions about formulas that return information in the furthest right cell

Hi everybody,

I found information on how to return information in the furthest right cell using the formulas below:
1. =LOOKUP(9^9,3:3)
returns the last number in row 3

2. =LOOKUP(REPT("z",255),3:3)
returns the last text entry on row 3

3. =LOOKUP(2,1/(3:3<>""),3:3)
returns the last non-blank cell.

I was wondering if anyone could answer the questions I had regarding these.

1. I don't understand what this formula is searching for. I get that it is looking in row 3, but how does 9^9(I don't know what this means) make it search right?

2. For this one, I don't understand how searching for z repeated 255 times returns the last text cell in row 3.

3. This is the one that confuses me the most. It appears to be searching for the number 2 in the range 1/(nonblank cells in row 3), and returning the value from row 3 that it finds. I don't understand what the (1/(3:3<>"") is telling it to search for, and how the number 2 is related to that.

I really appreciate any enlightenment anyone is willing to provide. I've tried searching through google, this forum, and Microsoft's site, but I just can't seem to find explanations that make sense to me.

Thank you,
Brad

Some things to understand about lookup.

It assumes the data in row 3 is sorted ascending from left to right
emphasize the word "assumes". It doesn't verify that it actually is sorted, it just assumes that it is..
When the data is NOT sorted, you get very strange results, that are difficult to understand (another whole conversation)
But a great effect of this is that if the lookup value is larger than all number in the lookup range, then it just returns the last number (furthest to the right)

That's how the first one works
9^9 = 9 "to the power of" 9 = 387420489...a VERY large number.
Whowever wrote that formula has assumed that number is larger than any number in row 3.
It's better to use 9.99999999999999E+307 instead of 9^9
Because that actually is the largest number that a cell can possibly hold.
9^9 is basically a lazy way to do it, but not actually accurate.


Now, the 2nd works basically the same, but with text instead of numbers.
Lookup still assumes data is sorted ascending.
and z is 'larger than' y
so it's assuming zzzzzz..255 zs....zzzz is 'Larger than' any text string in the range.


The 3rd one, that's the nice one because it works on both numbers and text.
The 1st one will only work on Numbers, then 2nd only works on Text, the 3rd works on either one..

3:3<>"" is basically a question, "Is the value in Row 3 NOT blank ?"
It repeats that question for all cells in row 3.
This creates an array of True and False answers to the question.
So you get 1/{true,false,true,true,etc..}
True = 1, False = 0
So it becomes
1/{1,0,1,1,etc}
1/1 = 1
1/0 = #DIV/0!
So it becomes
{1,#DIV/0!,1,1,etc}

So the whoe formula is now
=LOOKUP(2,{1,#DIV/0!,1,1,etc},3:3)

Lookup ignores errors, so it only sees the 1's.
2 is larger than all numbers in the array, so it returns the furthest one on the right.


Bada Bing, there ya go.

clear as mud right?

Thank you so much! That was an incredibly knowledgeable answer, and it really helped my understanding of lookup. They make perfect sense now.

You're welcome, thanks for the feedback.

Originally Posted by Tony Valko

If you want to know your most recent bowling score why on earth would you want to use 9.99999999999999E+307 as the lookup_value when you can simply use 301 (the largest possible bowling score is 300).

A couple reasons.
1. It is an industry standard, it's actually documented in the Excel Help files as the largest number allowed.
2. What if a mistake was made, and the highest score was input as 3001 ?

Using the standard bignum is an absolute, it is not possible at all for a number in the range to be larger than 9.99999999999999E+307.
It IS possible for a number in the range to be larger than 1E100 (not likely, but certainly possible)
9^9 is not really that big (relative to 9.99999999999999E+307 and even 1E100), less than a trillion.
In banking and national budgets, it is certainly possible to have numbers larger than that.

I'll choose impossible over unlikely every day of the week.


Your argument for 1E100 over 9.99999999999999E+307 is simply for ease of entry...
Saving keystrokes...
not taxing your memory when creating the formula.


While those are fine reasons to use 1E100 instead of 9.99999999999999E+307
It is certainly not wrong to use 9.99999999999999E+307 instead of 1E100, perhaps more difficult, but not wrong.

Your insistance that it 1E100 is a better choice than 9.99999999999999E+307, is actually a little rude.

It's one thing to answer a person's question using 1E100, that's fine.
But hijacking a thread that is already answered and insisting we use 1E100 instead of 9.99999999999999E+307..
That's another thing.

Originally Posted by Tony Valko

What sanctioning committee established this standard?

Microsoft, it's in the help files.

Rude? Seriously?

Yep

So, when someone offers another suggestion it's hijacking?

It's not another suggestion, it's the SAME suggestion with a trivial modification.

Show me where I insist anything?

OK..

Here we go again!

Indicating you've done this before..several times.
That's pretty insistent to me.


All I'm saying is that it's fine either way, 1E100 or 9.99999999999999E+307.
It's not ok for you to come behind and 'correct' someone's useage of one or the other.
I don't see anyone going behind your threads and saying use 9.99999999999999E+307 instead of E1001.