Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

[DocAElstein]

Using LOOKUP to get the last value
A solution
The development of an answer is a slightly back and forth chicken or egg thing: The initial investigations and observations from the last post have helped, but as the final solution discussed here was developed, those explanations were modified a bit to make the final full set of notes a bit clearer.

The last few post have suggested that the LOOKUP looking for a number bigger than it might ever find, is the main thing that is needed, Any numbers, preferably not too big in the column are OK
In the discussion so far we may have missed or overlooked something: So far we have considered numbers in a column, but of course for a general solution there could be any combination of text and numbers.
The solution that I am suggesting here, will use the LOOKUP in its 3 argument form, but unconventionally both the second argument and the third argument, which typically would be two similar size columns, will, in this solution, be the same column range. The third argument column range will be the simple range, unaltered, since as per the working of the LOOKUP, we will get the value from that, which we want, (the last value that is, hopefully, finally)
The key to the solution is unconventionally using that same column range for the second argument column range, but not in its original form, but somehow altered so as to
_ show numbers, or at the very least a number in the last used cell
_ above the last cell something such as text, that will be ignored by LOOKUP


So:
_ we know the first argument. It will be a number, perhaps a big one
_ we know the third/ last argument. It is simply the actual column range that we are trying to find the last value of

_ We are now concentrating on the second argument. We need to use in it the third/ last argument, modified in some way to
_ show numbers, or at the very least a number in the last used cell
_ above the last cell something such as text, that will be ignored by LOOKUP


Here we go:
Some general ideas and thoughts
We can change a value into a False or True thing, which can then either be changed to a 0 or 1, or may in some situations conveniently it may get changed automatically in some mathematical operation that will take False as 0 and take True as 1
(All that is just to do with how Excel, and to some extent computers in general, does certain things.

The solution
Assume we are looking for the last value in column C

Consider the result from this sort of thing if it is in an Excel formula or Excel function
C:C <> ""
(for clarity, I will just look here at a 11 entries in the column, and assume we are using just the first few cells in that C column )
FALSE
FALSE
TRUE
TRUE
TRUE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
Those results would come from if I had a last value in about the 8th row, after that row the logic of C:C <> "" is always false, since I have after that always an empty cell, which in simple logic may come out, as it does here, to be ""
**It can be a bit hit and miss with these things. As discussed in previous posts, an empty cell might be taken as 0 or "", but test suggest we are seeing "" here

Applying a logic of x 1 ( or using in some mathematical Excel thing that would itself convert True or false things to a 1 or 0), ( 1 x (C:C<>"") ) , would / could change that to
0
0
1
1
1
0
1
1
0
0
0
That is looking close to what we are looking for, but as we have seen, a 0 is a number, so our LOOKUP will end up returning us the value in the last cell in the range, which is 0. So whether we use some fixed range or the entire column C, the last few ( or thousands) of unused cells will contain a 0, and we will be returned the very last one. In other words we will get 0, and specifically Excel is giving us the value 0 from the very last cell (object***)in the range. That is not what we want. We want Excel to give us specifically the last filled cell.
( It might not do too much harm to get slightly technical at this point for later clarity*** Excel is indeed returning a specific cell range object. In the spreadsheet this concept is hard to show and in our case the LOOKUP will show us the value in it .)

But how about this: Rather than multiplying the range by 1, try dividing 1 by the range, thus 1/(C2:C12<>"")
The results from that, will look something of this form
#DIV/0!
#DIV/0!
#VALUE!
1
1
#DIV/0!
1
1
#DIV/0!
#DIV/0!
#DIV/0!
Technically speaking, where we do not have a 1, we have a specific Excel concept which is an Error type. In some things, such as VBA coding, that concept can be recognised. However, in a spreadsheet, Microsoft have organised that a text is given, specifically a text that gives us some indication of the error.
( In this case the #DIV/0! is probably telling us we had an empty cell, which was however in this case interpreted as a 0, so we have an error caused by 1/0 which pretty well errors in most things and systems and situations. The #VALUE! seems to come from text in a cell, and for some reason the TRUE was not interpreted as 1. However further testing suggests that in the final formula it was interpreted as 1, meaning that we can say that in the final analysis effectively it was at this stage more like this
#DIV/0!
#DIV/0!
1
1
1
#DIV/0!
1
1
#DIV/0!
#DIV/0!
#DIV/0!
**It is a bit hit and miss sometimes with these things, but at the end of the day here, we seem to have as if a 1 in any filled cell and the text of #DIV/0! for any empty cell.

All we need now is to use any number greater than 1 in the first argument, and then the LOOKUP will return us 1. But although this to us is indistinguishable form any of the 1s, Excel is actually giving us the last 1, (*** technically in its innards a cell range object is returned, but a spreadsheet can that concept not show so well, and we are simply shown the contents of that cell object ) and so if the actual unmodified C range is given in the third argument, then the actual last value in the range will be returned.

That’s it!


____________=LOOKUP(1.1,1/(C:C<>""),C:C)