Tom’s Tutorials For Excel: First and Last Values Along a Row


Share Button

Tom’s Tutorials For Excel: First and Last Values Along a Row

When you are faced with a table that is scattered with values among empty cells, you might need to know the first (left-most) or last (right-most) value in that row.

In the following picture, the first value in the row is returned by the formula
=INDEX(D2:H2,MATCH(TRUE,INDEX((D2:H2<>0),0),0)), copied down as needed.



In the next picture, the last value in the row is returned by the formula
=INDEX(2:2,MATCH(9.99999E+307,2:2)), copied down as needed.

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
6 comments on “Tom’s Tutorials For Excel: First and Last Values Along a Row
  1. Jackass says:

    Can anyone explain this formula? Whats with adding 307 to the exponent?

    • Tom Urtis says:

      Thanks for following my Excel blog.

      The 307 is not really being added. The number 9.99999999E+307 is the largest number that Excel can recognize. It is such a large number, it is expressed in scientific notation, easily recognized by the E notation. It is commonly used with LOOKUP or MATCH to find a last numeric entry in a column or row, or to identify the row or column number (Excel regards columns as numbers, despite how the grid headers usually look like A, B, C, and so on), where the last numerical value exists.

      Here, using row 2 as an example in the second picture, the formula =INDEX(2:2,MATCH(9.99999E+307,2:2)) returns 515 because the last numeric value is found to be in column 7 which is column G. You can test this yourself by replicating the table I posted, and enter this formula in an unused cell (not in row 2 to avoid a circular reference) which is a portion of the larger formula: =MATCH(9.99999E+307,2:2) which will return 7 which is column G. You can see that cell G2 holds the last numeric value of 515 and the formula proves it by looking along row 2 (the INDEX criterion) and finding the last used column with a number.

      • Matt says:

        Hi Tom,

        I was looking for a formula like this to find leftmost value in an array for both numbers and letters. I have one for the rightmost;

        =LOOKUP(2,1/(1-ISBLANK($D2:$H2)),$D2:$H2)

        Can this formula be modifed to find rightmost number or letter in this array?

        Thanks in advance. Matt

  2. Martin says:

    Hi Tom,
    I need to find the first and last value Against each batch no.. i’v data as follows..

    batch no Value
    1 12
    2 10
    3 09
    1 11
    1 10
    3 11
    2 09
    2 12
    3 10

    • Tom Urtis says:

      “First” and “last” meaning how they appear in order in the list? For example, for batch #2, the first would be 10 and the last would be 12? Just confirming your expected results, as opposed to maybe the min and max for those batches.

Leave a Reply

Your email address will not be published. Required fields are marked *

*