LOOKUP function (and finding the last filled cell in a column*)
Background/ History why I am doing it, - skip this if you are not interested, it is a bit of documentation for me mainky.
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, (Even though I did not find too many on a quick search). 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. )
* I edited it a bit around September 2025 to make it support better another question I was answering
Scope of the explanations (*changed a bit in September 2025)
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*
In this post, I will keep to the conventional, "normal" use and not try anything unusual.
Simplest 2 argument LOOKUP operation description
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
So note, that the "prescribed intention of use", as it were, is with numbers in the first column. Initially we will keep to that.
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 ( meaning that the numbers are ascending/ getting bigger ), (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.
Or an alternative explanation could just be that as soon as I hit a number bigger than I am looking for, then I take the last one.
There may be more to it than that, but that will be for advanced explanations and advanced operation descriptions
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.
( A parallel human like lateral way of thinking using letters could be 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 number 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
As giving numbers bigger than anything you have, it follows a similar or perhaps better said complimentary logic: 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
You could think of it as working as if there was some last extra very big number
=LOOKUP(999,{1;3;5;6;8;999999999})
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.
Operation description
So the first column range (second argument) (of numbers in conventional use ), is used as in the two arguments version 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 |
Just to avoid a bit of confusion due to the layout that I chose to use there: For the spreadsheet range version above ( in cell H15 ), the second argument first column range is the same as the previous example, E15:E19, the new third argument, second column, that is used for the output to be given, is in column range G15:G19.
This demonstrates, that the columns do not need to be next to each other. In fact the column ranges can be at offset rows
Perhaps this slightly different example will help clarify
Row\Col K L M 15 1 16 3 |1--3| 17 5 |3--5| 18 6 |5--6| 19 8 |6--8| 20 | > 8 | 21 22 |3--5|
Row\Col K L M 15 1 16 3 |1--3| 17 5 |3--5| 18 6 |5--6| 19 8 |6--8| 20 | > 8 | 21 22 =LOOKUP(4.9,K15:K19,M16:M20)
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 particular example2.
Reply With Quote
Bookmarks