Note: please do read up to the final paragraph, as the solution I start with is not optimal.
Say you want to have a formula that gives you the value of the last cell in column A.
The following formula is often found in the web and will accomplish this:
=LOOKUP(2;1/A:A;A:A)
Your data needs to meet a couple of prerequisites for this to actually work:
- your data needs to be numbers only
- all numbers must be greater than 0.5
So why does this formula work at all?
Let us look at the second parameter first: it creates a vector (you may think of it as a list) where each value is 1 divided by the value of the original vector (column A). So an original 2, 8, 3 will become 0.5, 0.125, 0.33333. An original value of 0.5 would yield 2, which is the number we are searching for (parameter 1) - if this existed, the whole thing would not work, hence the second prerequisite.
So when the value to search for (first parameter) is not found, as per the documentation of LOOKUP it will return the largest value that is less than the value to search for. If that were the case, the ormula would not work. Luckily, the documentation is not quite exact here.
Actually LOOKUP assumes a sorted vector (list). When it does not find an exact match, it actually returns the first value going up from the bottom that is smaller than the value to search for.
If our prerequisites are met, all values in the calculated list, which is 1(A:A), will be less than two. So, going up from the bottom, the first value it looks at is the last value in the list, and since all values are less than 2 it meets the criteria and is returned.
So now that you know how the formula works, you will be able to adapt it to your scenario.
N.B.: LOOKUP is a legacy function, it has officially been replaced by VLOOKUP and HLOOKUP. However, this neat trick will not work with either of the new functions. Let us hope that MS will support LOOKUP for a long time.
Finally, let's improve the formula, so it works without the limiting prerequisites mentioned above, it's easy enough:
=LOOKUP(MAX(A:A)+1;A:A)
This formula works without the limitations of the solution found on the web (except that it only works on numbers, duh) and is faster.
Lesson learnt: don't copy solutions from the web, try to understand the before you apply them. And under no circumstances should you re-post solutions that you don't fully understand, this is the worst kind of plagiarism.
