LOOKUP function and finding the last filled cell in a column
This is probably not worthy of a Tips, tricks or tutorials post, as it is a simple Excel standard function and there are almost certainly a lot more better Blogs, Videos etc. on it. I am also not initially looking at it extensively
But I needed it and a specific typical use of it, and it was not known to me, and so I made my own notes, and excelfox.com is a good place to access my stuff, and also the use of it was in ( another more substantial Blog posting, ) , where I came up with some similar formulas with VLOOKUP in a frenzy on Panzerschokolade, so can’t remember how I got it…..
So all in all it seemed OK to drop it in here, as I may need it to refer to later, possibly when others catch up with me, when/have I stop/ stopped taking those drugs.
( I actually stated using any of the Excel Loolup things , with VLOOKUP and had missed noticing this more fundamental LOOKUP. In the meantime there is the newer XLOOKUP, or some such, which like any new function excites the Microsoft loyal Excel addicts like excited children with a new toy.
But I am keen to encourage using just the basic fundamental functions as much as possible, ( and then thereafter, rather moving on to VBA / user defined functions for more complex things, so that gives me yet another reason for me posting it here. )
This first post will not be an extensive explanation of LOOKUP. It will just be enough to get me as far as moving on in the next post to explain a typically used formula / function version used to find the last filled cell in a column in a spreadsheet
Simplest 2 argument LOOKUP
For its simplest spreadsheet use we need a single row or column of numerical values, and for a simple explanation of its behaviour , this should be ascending in value, so something like this
1
3
5
6
8
If I apply the simplest version of LOOKUP to that example, and say I am looking in that column for a number of 4.9, then the function as written might look something like this, (assuming in this example I have the number 1 in cell E15, and am using the range E15:E19 for all of the 5 numbers )
=LOOKUP(4.9,E15:E19)
The final solution forms I am envisaging, are likely to involve array type things, so therefore as I proceed from here further, I will try to always show an array alternative, such as this alternative, using hard coded numbers rather than a spreadsheet range, as it will work on an array instead.
=LOOKUP(4.9,{1;3;5;6;8})
( , most spreadsheet formulas will work in some way or another with arrays, at least almost always in a spreadsheet.
Since Excel tends to update the whole spreadsheet, or at least a large part of it, it is often efficient to take advantage of this, which is what the array calculation concept in Excel is based on )
This would be how the test range and the LOOKUP formulas looks in a spreadsheet
Row\Col E F 15 1 =LOOKUP(4.9,E15:E19) 16 3 =LOOKUP(4.9,{1;3;5;6;8}) 17 5 18 6 19 8
In both cases the answer you would see would be 3, like this:
Row\Col E F 15 1 3 16 3 3 17 5 18 6 19 8
Perhaps it is not obvious why we get 3 when looking for 4.9 ?
To Explain:
What seems to be happening is that it assumes the numbers go up in value as you go along/down ( ascending ), (as we have in this example), and it will either give back the value if it finds it, or otherwise it gives you the next value down.
So it is looking for the nearest match, … err .. or .. ? - .. well not quite… it is looking for the value, (and if it finds it , then it will return that number), but if it does not find it, it gives you the nearest value next one down.
Another way of looking at this is to say we get a value out for a certain range of numbers we might give in the first argument, and this returned number is the lowest number in that certain range, like A to C goes in tray A, D to F goes in try D, F to W goes in tray F etc…
In the example above, something approximately as in this sketch form
Spread of numbers
We might give
In the first argument ----- value returned by VLOOKUP ( tray identificater )
___1 - 2.999 --------------- 1
___3 – 4.999 --------------- 3
___5 – 5.9999 -------------- 5
___6 - 7.999 --------------- 6
( If you give in this example anything above 8 in the first argument in the LOOKUP function , then the LOOKUP function will return 8 )
____> 8 …….. --------------- 8
3 argument LOOKUP
In the previous example the "nearest", (nearest lowest), value in the second argument column range, that nearest to the first argument value you gave, was the output. (Or indeed the exact value you gave , if that value was in the list).
By adding a new third argument as a similar sized range, the output will then be the value at the corresponding row/position along in that new third argument range. This new extra range can be a range of numbers or text or both.
So the first column range (second argument) is used to find the "nearest" row/position along that the number you give as first argument "belongs" to , (In other words the number range the number you give as first argument is in, as per the sketch above), but the output now LOOKUP gives is the value in the corresponding row in the second range column, (third argument).
So effectively you can determine what output is given for a range of numbers given in the first argument.
For example it can be used to give an output to tell you in which number range your number was. Extending/ following on from the last example…..
Row\Col E F G H 15 1 3 |1--3| |3--5| 16 3 3 |3--5| |3--5| 17 5 |5--6| 18 6 |6--8| 19 8 | < 8 |
Row\Col E F G H 15 1 =LOOKUP(4.9,E15:E19) |1--3| =LOOKUP(4.9,E15:E19,G15:G19) 16 3 =LOOKUP(4.9,{1;3;5;6;8}) |3--5| =LOOKUP(4.9,{1;3;5;6;8},{"|1--3|";"|3--5|";"|5--6|";"|6--8|";"| < 8 |"}) 17 5 |5--6| 18 6 |6--8| 19 8 | < 8 |
I will leave the explanation of the function at this stage, possibly me or someone may come back in further posts after the next ones, later, or probably much later, if at all..
For now I want to concentrate on a particular example.
Reply With Quote
Bookmarks