LOOKUP function (and finding the last filled cell in a column*)

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.

---

spare post

( * Using LOOKUP to get the last value in a column )
Some general ideas and observations

For the particular formulas that I was / *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, - the one used to effectively select the output. The output can be from that column or any other: Note in passing that the particular formula there,
=LOOKUP(2;1/(C:C<>"");C:C)
, is a strange mutation of what was discussed in the first post. It is the three argument version.
The third argument for the possible outputs, looks sensible, - it is simply column C
But the second argument is some strange formula which includes in it the column C ?!??

A few observations/characteristics :
_observations/characteristics
A couple of points from the first post: VLOOKUP seems to assume
_ assume that the list/ range of numbers is in ascending order. Or at least it helps us to understand it, if we think it does.
_ based partly on the last point, and the other basic characteristics from the first post, looking for a bigger number than we have will end up giving us the last number in the column even if a closer number is further down

In other words it "keeps going" in the case of a bigger number that its anywhere, and we get given the last number

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/characteristic
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 it would error, because I was thinking that the second argument would be always expecting numbers). Rather than erroring, 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 as 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 in an Excel spreadsheet, 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 to 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, using what we have observed and what we understand as the workings of LOOKUP

spare post

* September 2025 Coming back here to add to and modify answer to get a solution to use in a different question that I was answering
In the meantime I can see a lot more similar Blogs on the internet. Some may have appeared in the meantime. Others maybe show up as Google and Microsoft & co. are spying on me and presenting me what they think I want to see

---

---

---

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.

Some of 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 being looked in, ( that column being the second argument 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, either the simple column range or some variation of it.

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 for a neat idea got from a bit of erratic playing around….. Rather than multiplying the range by 1, try dividing 1 by the range value, 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)