Can you please explain the formula?

Let's use your sample in this explanation:

Cols A B C D E F G H
RowA: 1 2 1 * * 5 * *

=ADDRESS(ROW(),LOOKUP(10^10,A1:H1,COLUMN(A1:H1)),4 )

The ADDRESS function takes these arguments:

=ADDRESS(row_number,column_number,reference_number )

The row_number argument is the ROW() function. Since the ROW() function
doesn't contain any arguments it will reference the row that the formula is
entered on. So, if that formula was entered in cell J1 the row_number would
be 1. If that formula was entered in cell J100 the row_number would be 100.

The column_number argument is LOOKUP(10^10,A1:H1,COLUMN(A1:H1)).

This function has 3 arguments: lookup_value, lookup_vector and
result_vector.

What we need to do is find the last numeric value in the range A1:H1.

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

To ensure that the lookup_value is greater than all the values in the
lookup_vector we use an arbitrary number that is guaranteed to meet this
condition. In this case the lookup_value is 10^10 (10 to the 10th power) or
10,000,000,000.

The *last* value in the lookup_vector that is less than the lookup_value is
the number 5.

So, the function returns the value from the result_vector that corresponds
to the *last* value in the lookup_vector that is less than the lookup_value.
The values in the result_vector are the column numbers: COLUMN(A1:H1). It
would look like this:

....A..B..C...D...E..F..G..H
....1...2...3...4...5...6...7...8 (result_vector)
....1...2...1.............5......... (lookup_vector)

The result of the LOOKUP function is 6.

At this point here's what the ADDRESS formula looks like:

=ADDRESS(1,6,4)

The 4 means to return a relative reference.

So the final result is column 6 row 1 = F1 is the location of the last
number in that range.

Biff

wrote in message
oups.com...
Excellent. Thank you.

Can you please explain the formula?