Hi Biff

I quite agree, but to ensure it is a big enough number then 99^99
usually suffices, and that is easy enough to use and remember.
Alternatively, I often define a name like bignum with Insert
NameDefine bignum
Refers to 9.99999999999999E+307
so I don't have to think about the large number and how many 9's or what
exponent in any subsequent formulae

then use =MATCH(bignum,'Data'!A7:BA7)-1

--
Regards

Roger Govier


"T. Valko" wrote in message
...
I have no clue either, except the guy is a bit weird.

Not really, he's just "following the herd"!

The way that Lookup works is that if the lookup_value
(9.99999999999999E+307) is not found the result of the formula is the
*LAST* value in the range that is less than the lookup_value
(9.99999999999999E+307). Since it is pretty much a guarantee that
every number in the range will be less than 9.99999999999999E+307 the
last number in the range is returned.

This is something that hits a nerve with me. Suppose the numbers in
your range are golf scores. Depending on how good the players are
there is absolutely no chance that any score will be greater than 125.
In this case the lookup_value can be something like 200 rather than
the pedantic 9.99999999999999E+307 (which I'm sure confuses a lot of
people). How many 9's do I have to type? ?????? <argh

Now, concerning your question. It's not clear what you're wanting to
do.

=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1

That formula will return the relative position of the last number in
the range minus 1.

A7 = 1
B7 = 10
C7 = 5
D7 = 2

The above formula would return 3. 2 is the last number that is less
than the lookup_value. It's relative position is 4, minus 1 = 3.

Biff

"Steve" wrote in message
...
I have no clue either, except the guy is a bit weird. You would think
200
zeros would be enough :)


"Dave F" wrote:

9.99999999999999E+307 is scientific notation, i.e., approximately 10
*
10^307, or approximately 10 followed by 307 zeroes. Why someone is
trying to
use a formula that considers such a large number, I have no clue.

Dave
--
A hint to posters: Specific, detailed questions are more likely to
be
answered than questions that provide no detail about your problem.


"Steve" wrote:

I'm trying to figure out someone else's workbook. This is the
formula that
produces the correct contents of the cell in data!V4 on a
different
worksheet.
=MATCH(9.99999999999999E+307,'Data'!A7:BA7)-1
How do I change this formula to obtain the contents of cell
data!V5 ? & V6
The E and the +307 has me most perplexed.
The A7:BA7 is number data that is entered weekly, and the V column
is the
last of the data for this week, so when next week's data is
entered, the
contents of cell data! W4 will be produced, and then I'd want the
formula to
obtain the contents of W5 & W6.

Thanks,

Steve