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

[DocAElstein]

Using LOOKUP to get the last value

For the particular formula that I am trying to get to and understand, ( one of this sort of form =LOOKUP(2;1/(C:C<>"");C:C) ) ,I need to look a bit further into the behaviour of LOOKUP, in particular the second argument.

A few observations:
_observation
VLOOKUP seems to assume that the list/ range of numbers is in ascending order
For example, this sort of thing,
1
3
4
2

Row\Col	E
37	1
38	3
39	4
40	2
41
42

, with the LOOKUP formula of these sort of things
=LOOKUP(5;E37:E40)
=LOOKUP(5;{1;3;4;2})
, will return 2. In fact it always seems to return the last value. This initially points us into perhaps a direction of thinking to just look for any number likely to be bigger than any in the list of numbers (in the second argument range)

_observation
Extend the range a bit , to example include some empty cells,
=LOOKUP(5;E37:E43)
, and once again the answer is 2.
This is very interesting and helpful. It seems not to error when not getting numbers, (as I was thinking that the second argument would be always expecting numbers). The empty cells simply seem to be ignored

But at this point some alarm bells are ringing in my head. The final solution forms I am envisaging, are likely to involve array type things, so I want to continue as I have done so far, to consider the array alternative, and I am getting worried now – because…
depending on your viewpoint, an empty cell can be regarded and one if at least 3 things I know about
___ Empty
___ 0
___ a zero length string, often represented by ""

Well, I can’t be too sure about how to distinguish all 3 in a formula, but I can do a partial job by considering these two array formula versions
=LOOKUP(5;{3;4;2;0;0;0}) ' returns 0
=LOOKUP(5;{3;4;2;"";"";""}) ' returns 2

The difference in those two results is enough for me to ponder about for a while. But I don’t need tom ponder too long.
It makes sense where we get 0, since 0 is a number, and in line with what we have already said
_____ VLOOKUP takes the very last number in a list if it never finds either the number it is looking for , or if it never finds a larger number at any other position in a list, (in this latter case it would take the number before that bigger number)
For the purposes of the array type formula, "" , can be regarded, (with some caution perhaps), of behaving like our spreadsheet empty cell. We will come back to this last point, but let’s give a pictorial summary so far of where we are

Row\Col	E	F
37	1	=LOOKUP(5,E37:E40)
38	3	=LOOKUP(5,{1;3;4;2})
39	4	=LOOKUP(5,E37:E43)
40	2	=LOOKUP(5,{3;4;2;0;0;0})
41		=LOOKUP(5,{3;4;2;"";"";""})
42
43

Row\Col	E	F
37	1	2
38	3	2
39	4	2
40	2	0
41		2
42
43

---

In Excel functions and formulas generally, the difference between using a number and a text can be significant. The fact that the array version both accepts ( does not error with ) the "" , and gives us perhaps a useful answer , the last number, could be useful….

Also it may be worth checking what happens if the text is not zero length.
Here we go, lets try filling the empty cells with some text:

Row\Col	E	F
37	1	=LOOKUP(5,E37:E41)
38	3	=LOOKUP(5,{1;3;4;2})
39	4	=LOOKUP(5,E37:E43)
40	2	=LOOKUP(5,{1;3;4;2;0;0;0})
41	a	=LOOKUP(5,{1;3;4;2;"a";"b";"c"})
42	b
43	c

Row\Col	E	F
37	1	2
38	3	2
39	4	2
40	2	0
41	a	2
42	b
43	c

Hmm…. So it seems to treat an actual text just like the zero length text



In the next post we will develop/ explain a solution