Just a comment:
9.99999999999999E+307 and 2^15 are hugely excessive but this does not affect anything.
The number should be >= the maximal expected value of SEARCH(substring,string).
So, for most practical purposes a 100 would be just enough.
It's
LOOKUP(MAX(
reference)+DELTA,
reference)
where DELTA a small positive number such that MAX(
reference) < MAX(
reference)+DELTA.
Although this will yield the last numeric value from
reference, it's not desirable on two counts:
[1] Slow because MAX examines every cell of
reference.
[2] MAX is sensitive to the error values like #N/A in
reference.
The big number
9.99999999999999E+307
is a limit value of Excel itself. It's quite improbable that this value will ever occur anywhere in
reference. Thus there won't be any need to guess what would be a suitable number for a given reference whenever we are after the last numeric value. So there is no need for any of the zillions variations like 2^15, 99^99, 1E100, etc. etc.
[3] We know that LOOKUP and kindred functions ignore error values if they possibly can (they are designed that way).
[4] LOOKUP (and kindred functions with match-type TRUE or 1) appear to recruit (a form of) the binary search algorithm, which is very fast.
Here are some relevant links on the matter:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998
Given 1 to 4, we have formulas like...
=LOOKUP(9.99999999999999E+307,A:A)
=IFERROR(B2/LOOKUP(9.99999999999999E+307,SEARCH($D$2:$D$4,A2),$E$2:$E$4),"")