What is this programmer doing with his Lookup func

Question

I found on a forum a formula to find the last populated cell in a column:

LOOKUP(2,1/(G:G<>""),ROW(G:G)))

But what's going on with this bit?

1/(G:G<>"")

One divided by ??? (something that's not equal to ""?) I don't understand the logic, here.

Answer 1

If you want to observe the calculation step by step by evaluating the formula with Formula Auditing I strongly recommend limiting the range. Say apply:

=LOOKUP(2,1/(G1:G10<>""),ROW(G1:G10))

And, for illustration purposes, populate no lower down the sheet than say G6 (but a void between, in the range G1:G5, may in that case help to understand what is happening).

For this answer I am only going to consider five cells: G1, G3 and G4 populated, G2 and G5 (onwards) not.

1/(G1:G5<>"")

Is indeed at the heart of this formula. G1:G5<>"" does, as you have recognised, test whether not equal to "". "" is the convention for 'empty' for an Excel cell. If populated (ie "not empty") this returns TRUE and FALSE otherwise. Hence for the five cells as chosen for this example an array is returned, regarding G1:G5 in order, of:

TRUE;FALSE;TRUE;TRUE;FALSE.

In arithmetic calculations Excel treats TRUE as 1 and FALSE as 0. Hence using the above truth table as the denominator and 1 as the numerator gives an array (again in order) of:

1/1;1/0;1/1;1/1;1/0

which resolves to:

1;#DIV/0!;1;1;#DIV/0!.

In the LOOKUP function above 2 was chosen as the lookup_value. (Any other number greater than 1 would serve equally well.) So we are looking for 2 in an array that is composed exclusively of either 1s or errors. Therefore there is no chance of finding an exact match, so the default kicks in, which is the last value (in order, not counting errors). The last 1 in the array is the fourth element, and the fourth element in ROW(G1:G5) is …4.

G4 is the last populated cell in ColumnG (in my example).