Quote Originally Posted by dafacta View Post
Curious, what does the 9.9999999 number do in your suggestion?
LOOKUP(9.99999999999999E+307,Reference)

turns up the last numeric value from Reference if any. It's of significance that the look up value cannot occur in references of interest to us. This 9.99999999999999E+307 is the largest allowed positive number in Excel and for that reason is a good candidate.

See:

http://www.mrexcel.com/forum/excel-q...9999999-a.html
http://www.mrexcel.com/forum/excel-q...-returned.html (post #7)