Pekkavee said:

...

I see this

9.99999999999..

to be used now and then in formulas like

=IF((Sheet2!D2<>"")*(Sheet2!D2=$A$1),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")

What is the function of that. I know it works in those cases I have seen but I would like to learn how to use it and why?...

A) The algorithm, known as binary search (BS), finds the largest value that is less than or equal to the search value in a reference.

B) Lookup functions like:

MATCH(LookupValue,Reference,MatchType), with MatchTpe = 1,

VLOOKUP(LookupValue,Reference,1,MatchType), with MatchType = 1,

HLOOKUP(LookupValue,Reference,1,MatchType), with MatchType = 1,and

LOOKUP(LookupValue,Reference)

all invoke binary search and attempt to return a result, conditioned upon the fact that matching/binary search finds the largest value that is less than or equal to the lookup value.

C) 9.99999999999999E+307 is the largest positive number Excel knows (hereafter, referred to as BigNum).

D) BigNum is an improbable value to occur in references of interest to us.

Given A-D, whenever there is a need to retrieve the last numerical value from a reference [1] or to determine the position of the last numerical value in a reference [2], we can invoke for...

[1]

=LOOKUP(BigNum,Reference)

=INDEX(Reference,MATCH(BigNum,Reference))

=VLOOKUP(BigNum,Reference,1), and for...

[2]

=MATCH(BigNum,Reference)

With MatchType omitted, Excel defaults to 1 (or TRUE). LOOKUP() is designed, as it were, to operate under MatchType = 1.

Note that BigNum is preferred instead of MAX(Reference)+1, which would play an identical role in the foregoing formulas, for reasons of efficiency: not too loose processing cycles with MAX.

For more, search on this board and thru Google.