Any similar tricks to BigNum and Lookup(2,1/(A:A)..)....

[freegg123]

I have been reading posts about the usage of BigNum and Lookup(2,1/(A:A)..), basically the idea is to return the last number in the column if the lookup value is not in the column. Actually you do not need to understand BigNum as a pre-defined concept, you can use any number as long as it is not in the range you select, the function will always return the last number in the column.

It is fascinating, thanks to Aladin the Excel MVP. And I am just wondering are there other such inexplicit tricks that exist in Excel, can real guru post a library of these?

Many thanks in advance

 

[ChrisBM]

I am afraid I cannot provide any of these and merely hope to ask where you were reading about these tricks?

 

[freegg123]

try here:

http://www.mrexcel.com/forum/excel-questions/636086-using-lookup-2-1-2-criteria.html

 

[Aladin Akyurek]

I have been reading posts about the usage of BigNum and Lookup(2,1/(A:A)..), basically the idea is to return the last number in the column if the lookup value is not in the column. Actually you do not need to understand BigNum as a pre-defined concept, you can use any number as long as it is not in the range you select, the function will always return the last number in the column.

It is fascinating, thanks to Aladin the Excel MVP. And I am just wondering are there other such inexplicit tricks that exist in Excel, can real guru post a library of these?

Many thanks in advance

I am afraid I cannot provide any of these and merely hope to ask where you were reading about these tricks?

[A] BigNum stands for 9.99999999999999E+307

BigStr stands for REPT("z",255)

They serve as look up value in formula expressions which are meant to pick out the last numeric value [A] and the last text value .

LOOKUP(2, 1/...) is the same as LOOKUP(BigNum, 1/...)

Some relevant links:
1) http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
2) http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998
3) http://www.mrexcel.com/forum/hall-f...ureks-lookup-construct-return-last-match.html