To find the last numeric value in a row that may include blank cells, use this formula;

=INDEX(row_lookup_range,MATCH(9.99999999999999E+307,row_lookup_range))

=INDEX(A4:M4,MATCH(9.99999999999999E+307,A4:M4))

The row_lookup_range is the range of cells (A2:M2) in a single row or an entire row (2:2) in which we want to find the last value. The clever thing about this formula is the way it uses the MATCH function. MATCH(lookup_value,lookup_range,match_type)

If you are familiar with Excel’s lookup functions, you will know that they can be used to find an exact match or a closest match. In the MATCH function, this is determined by the third argument of the function, which by default (i.e. if omitted from the formula) is 1, and finds the largest value that is less than or equal to the lookup_value. In most cases with the MATCH function, the values are required to be in ascending order. However, for this solution we are not required to have the values in ascending order but will instead use this to help solve our problem.

Explanation of MATCH(9.99999999999999E+307,row_lookup_range):

This is actually pretty simple (though very clever). Here we use a lookup_value (9.99999999999999E+307 – the highest possible value in Excel) that is higher than any value that will be in our row_lookup_range. In fact, you could use MATCH(1000000,row_lookup_range) if the maximum value in the range will always be less than a million.

When the MATCH function looks through the row_lookup_range for the lookup_value, it doesn’t find it. Then by default (the match_type argument of the function is omitted) it looks from right to left in the row_lookup_range and returns the last numeric value that is less than or equal to the lookup_value, which turns out to be the last value entered in the row.

To find the last NUMERIC value in a ‘column’ we adapt the same formula to be:

=INDEX(A2:A100,MATCH(9.99999999999999E+307,A2:A100))

To find the last TEXT value in a row we can use:

=INDEX(A2:M2,MATCH(REPT(“Z”,255),A2:M2))

To find the last TEXT value in a column we can use:

=INDEX(A2:A100,MATCH(REPT(“Z”,255),A2:A100))

If our row_lookup_range does not include blanks, we can take a simpler approach with these formulas…

For last value in a row that has no blank cells:

=INDEX(A2:M2,COUNTA(A2:M2),1)

For last value in a column that has no blank cells:

=INDEX(A2:A100,COUNTA(A2:A100),1)

### Like this:

Like Loading...