The term "BigNum", which stands for "Big Number", is used to represent the largest allowed positive number in Excel, which is 9.99999999999999E+307
BigNum is used in certain lookup formulas constructed in a way to find the largest value that is less than or equal to a search value. Because BigNum is an improbably large value, the lookup will find the previous numeric value.
For example, you can use the following formula to find the position of the last numeric value in a column:
=MATCH(9.99999999999999E+307,range)
In practice, any improbably large value will do, so you could trim the above to:
=MATCH(9.99E+307,range)
The reason this works has to do with the default behavior of MATCH, which uses a binary search algorithm to find the largest value that is less than or equal to the search value in a range. In this (default) mode, MATCH assumes values are sorted in descending order. When BigNum is, by design, not found, MATCH returns the position of the last number in the range.
For more details, see: Last row in numeric data.
BigNum with other lookup functions
The BigNum approach can be used with other functions that support binary search, for example:
=MATCH(bignum,range,1)
=VLOOKUP(bignum,range,1,1)
=HLOOKUP(bignum,range,1,1)
=LOOKUP(bignum,range)
Note match type is set to 1 in the first three examples to explicitly set binary search, often referred to as approximate match.
