Here is the formula again...
=LOOKUP(2,1/(C1:C14<>""),C1:C14)
The LOOKUP function will attempt to find the first argument (2), in the array of values found in the second argument. Let's look at the denominator of that second argument... it is a logical expression... each cell in the specified range will be compared to the empty string ("").... if it is **not** equal to it, TRUE is generated and if it is equal to it, FALSE is generated. Whenever TRUE or FALSE is used in a numerical calculation, Excel converts them to 1 and 0, respectively, before performing the math operation involving them. The math operation, in this case, is to divide the result from the logical expression into 1. Since the only two possible values are 1 and 0, an array of 1/1 and 1/0 is generated. The 1/1 will evaluate to 1; however, the 1/0 division will produce a #DIV/0! error because you cannot divide a number by 0 and get a valid result. So the LOOKUP will attempt to find the 2 from the first argument in the array of 1s and #DIV/0! errors from the second argument. If the LOOKUP formula cannot find the first argument in the array of values from the second argument,it uses the largest value in the array that is less than or equal to first argument and, if there are ties for this largest value, it uses the last of them for its match. The largest such value will, in the case of our array, will be a 1 and the last of them will always be generated by the last piece of data since it will be the last cell not equal to the empty string.