[Mark]

Hello

I understand what the above function does, however I would like to know how
it works, in obtaining the first available cell with data in the range from
right to left....Thanks Mark

[L. Howard Kittle]

Hi mark,

Put 1 through 8 in A1:H1.

In A4 enter =LOOKUP(9.99999999999999E+307,A1:H1)

This returns 8.

In A5 enter =LOOKUP(9,A1:H1)

This also returns 8.

As long as the Look_Up value is larger than any value in the Look_Up Vector,
it will return the last number in the vector.

Now change D1 to 500. A4 returns 8 and A5 returns 3. The A4 lookup value
is larger than 500 so it returns 8. The A5 lookup value is less than 500 so
it returns the 3.

Beam up help on the worksheet and check out LOOKUP and parse the examples
and the lookup rules.

The huge lookup value in the A4 formula is, I believe, the largest number
Excel will deal with, so it would, of course, return the last value in the
vector no matter how large it was. But the lookup value really only needs
to be larger than any value anticipated within the lookup vector to return
the last value in the vector.

HTH
Regards,
Howard

"Mark" wrote in message
...
Hello

I understand what the above function does, however I would like to know
how
it works, in obtaining the first available cell with data in the range
from
right to left....Thanks Mark

[Joe User[_2_]]

"Mark" wrote:
Subject: = LOOKUP(9.99999999999999E+307,A1:H1)

I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left

First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.

Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault; you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest cell
value, there is no point to entering a constant with such precision. 9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].

Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e. the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.

This use of LOOKUP is based on the assumption that all cell values are less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:

1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.

2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.

-----
Endnotes

[1] The largest cell value is about 1.79769313486231*10^308. If you want to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970). In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.


----- original message -----

"Mark" wrote:
Hello

I understand what the above function does, however I would like to know how
it works, in obtaining the first available cell with data in the range from
right to left....Thanks Mark

[Joe User[_2_]]

"Joe User" wrote:
1. In this form ("array" form), LOOKUP finds the largest
value less than or equal to the lookup value in the first
row of the lookup array. Assuming a sorted range,
presumably LOOKUP starts at the end of the range and
searches linearly.

I should clarify....

Since LOOKUP assumes sorted data and it searches from the end of the range,
the first number that LOOKUP finds is presumed to be "the largest value less
than or equal to the lookup value", even if it isn't.

Moreover, in its search from the end of the range, apparently it ignores
empty cells. And when the lookup value is a number, cells with text and
boolean values (TRUE, FALSE) are effectively ignored because text and boolean
values are always considered to be greater than any number.


----- original message -----

"Joe User" wrote:
"Mark" wrote:
Subject: = LOOKUP(9.99999999999999E+307,A1:H1)

I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left

First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.

Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault; you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest cell
value, there is no point to entering a constant with such precision. 9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].

Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e. the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.

This use of LOOKUP is based on the assumption that all cell values are less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:

1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.

2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.

-----
Endnotes

[1] The largest cell value is about 1.79769313486231*10^308. If you want to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970). In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.


----- original message -----

"Mark" wrote:
Hello

I understand what the above function does, however I would like to know how
it works, in obtaining the first available cell with data in the range from
right to left....Thanks Mark

[T. Valko]

9.99999999999999E+307 is silly, IMHO.
Not your fault; you're just following someone
else's instructions.

Agreed.

I used to follow the herd but I go my own way these days!

1E100 is short, easy to remember and works just as well as
9.99999999999999E+307.

I challenge anyone to find a *REAL-WORLD APPLICATION* where 1E100 fails (is
too small of a number).

Does anyone work with numbers that big? If so, who? NASA maybe, but I'll bet
for those kind of numbers they're not using Excel.

--
Biff
Microsoft Excel MVP


"Joe User" <joeu2004 wrote in message
...
"Mark" wrote:
Subject: = LOOKUP(9.99999999999999E+307,A1:H1)

I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left

First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.

Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault;
you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest
cell
value, there is no point to entering a constant with such precision.
9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].

Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e.
the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.

This use of LOOKUP is based on the assumption that all cell values are
less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:

1. In this form ("array" form), LOOKUP finds the largest value less than
or
equal to the lookup value in the first row of the lookup array. Assuming
a
sorted range, presumably LOOKUP starts at the end of the range and
searches
linearly.

2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.

-----
Endnotes

[1] The largest cell value is about 1.79769313486231*10^308. If you want
to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970).
In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.


----- original message -----

"Mark" wrote:
Hello

I understand what the above function does, however I would like to know
how
it works, in obtaining the first available cell with data in the range
from
right to left....Thanks Mark

[Charles Williams]

On Sun, 31 Jan 2010 16:23:01 -0800, Joe User <joeu2004 wrote:


1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.


LOOKUP and the sorted data options of VLOOKUP, HLOOKUP and MATCH use a
Binary Search algorithm with the collating sequence appropriate to the
current Locale.
The unsorted data options use Linear Search starting at the first row.

Binary search is much faster than linear search for anything more than
a trivial amount of data.

Charles Williams

[Joe User[_2_]]

"Charles Williams" wrote:
On Sun, 31 Jan 2010 16:23:01 -0800, Joe User <joeu2004 wrote:
presumably LOOKUP starts at the end of the range and searches
linearly.

LOOKUP and the sorted data options of VLOOKUP, HLOOKUP
and MATCH use a Binary Search algorithm

One would hope so; and based on my experiments, I believe you are correct.

I should have said "conceptually" instead of "presumably". I think it is
hard to convince someone who is unfamiliar with these algorithms that a
binary search will always find the last number if the lookup value is
greater than all of the numbers, even if the data is unordered.

Better still, I shouldn't have tried to explain the search algorithm in the
first place. Mea culpa times three.


----- original message -----

"Charles Williams" wrote in message
...
On Sun, 31 Jan 2010 16:23:01 -0800, Joe User <joeu2004 wrote:


1. In this form ("array" form), LOOKUP finds the largest value less than
or
equal to the lookup value in the first row of the lookup array. Assuming
a
sorted range, presumably LOOKUP starts at the end of the range and
searches
linearly.


LOOKUP and the sorted data options of VLOOKUP, HLOOKUP and MATCH use a
Binary Search algorithm with the collating sequence appropriate to the
current Locale.
The unsorted data options use Linear Search starting at the first row.

Binary search is much faster than linear search for anything more than
a trivial amount of data.

Charles Williams

[Mark]

Thanks for your help...Mark

"L. Howard Kittle" wrote:

Hi mark,

Put 1 through 8 in A1:H1.

In A4 enter =LOOKUP(9.99999999999999E+307,A1:H1)

This returns 8.

In A5 enter =LOOKUP(9,A1:H1)

This also returns 8.

As long as the Look_Up value is larger than any value in the Look_Up Vector,
it will return the last number in the vector.

Now change D1 to 500. A4 returns 8 and A5 returns 3. The A4 lookup value
is larger than 500 so it returns 8. The A5 lookup value is less than 500 so
it returns the 3.

Beam up help on the worksheet and check out LOOKUP and parse the examples
and the lookup rules.

The huge lookup value in the A4 formula is, I believe, the largest number
Excel will deal with, so it would, of course, return the last value in the
vector no matter how large it was. But the lookup value really only needs
to be larger than any value anticipated within the lookup vector to return
the last value in the vector.

HTH
Regards,
Howard

"Mark" wrote in message
...
Hello

I understand what the above function does, however I would like to know
how
it works, in obtaining the first available cell with data in the range
from
right to left....Thanks Mark


.

[Mark]

Joe

Thanks for your help...Mark

"Joe User" wrote:

"Mark" wrote:
Subject: = LOOKUP(9.99999999999999E+307,A1:H1)

I understand what the above function does, however
I would like to know how it works, in obtaining the
first available cell with data in the range from right
to left

First, please repeat the details of the Subject line in the message body.
Not all NG readers show the entire Subject line in all contexts.

Second, typing 9.99999999999999E+307 is silly, IMHO. Not your fault; you're
just following someone else's instructions. True, that is the largest
constant that we can enter into a cell. But since it is not the largest cell
value, there is no point to entering a constant with such precision. 9E307
is probably sufficient, if you want a constant; 10^308 is probably
sufficiently better [1].

Third, apparently you do not understand what the function does, after all.
It finds the last value in the range, not the first available cell (i.e. the
cell after the last value). It does that even if the cell range is not
sorted in ascending order, which is normally a requirement for LOOKUP.

This use of LOOKUP is based on the assumption that all cell values are less
than the lookup value (e.g. 10^308). Reading the LOOKUP help page should
then give you the explanation you require. Namely:

1. In this form ("array" form), LOOKUP finds the largest value less than or
equal to the lookup value in the first row of the lookup array. Assuming a
sorted range, presumably LOOKUP starts at the end of the range and searches
linearly.

2. In this form of LOOKUP ("array" form), LOOKUP returns the value in the
same position of the last row of the lookup array that corresponds to the
value found in #1. In this usage, since there is only one row, the "last"
row is the same as the "first" row.

-----
Endnotes

[1] The largest cell value is about 1.79769313486231*10^308. If you want to
enter a similar number, but with fewer digits, be sure to truncate, not
round. Rounding will result in a #NUM error because the resulting value
would be too large. The actual largest cell value is 2*(2^1023 - 2^970). In
VBA, but not in Excel, you would write 2*2^1023 - 2^971.


----- original message -----

"Mark" wrote:
Hello

I understand what the above function does, however I would like to know how
it works, in obtaining the first available cell with data in the range from
right to left....Thanks Mark