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: In practice, any improbably large value will do, so you could trim the above to: 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: Note match type is set to 1 in the first three examples to explicitly set binary search, often referred to as approximate match. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
