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

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
9 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.

  3. JTC says:

    Hello,
    I have a spreadsheet that tracks investigations at different facilities. I am trying to find a formula that can give me the most up to date status of the facility. Here is some background: Every time the site is inspected, a particular operating status is evaluated and “T” or “F” is entered in the cell. Since the status can change from time to time, I would like to capture the most up to date status. The spreadsheet is designed with one row per site so my range will have to be across the row. i.e. I am looking at the last time a T or F was entered in that row. I hope this makes sense.

    Thanks in advance for your help!

    • Tom Urtis says:

      Do you only want to know which character (a T or an F) is the last character along a row? Or, when you say you want the most up to date status, do you need to know the date and time that the row was last updated? I am not sure what you are asking for, all of which is possible, if you can give a bit more detail about what your desired result would actually tell you.

  4. Chris Clavin says:

    Hi Tom,

    I like your website and really appreciate the work that you do to help us less-experienced folks muddle through the intricacies of Excel. With that said I have a similar question. I am trying use an excel formula to return to me the last value (a date) in a row of fields that have either blanks or a date. The last column on the right would have the latest date entered for that Row’s columns. For instance the header would be the months of Jan-Dec across the top and down the left column would be all of my clients. Any month in which a client met with me would have a date entered for that client’s row in the proper month’s column. I want a column off to the right (after December) that would quickly show me all of the last dates where I met a client so I can readily view the need to set up a meeting. Any Help. Example:

    Client Jan Feb Mar Apr May Solve – Lastest meeting?
    Jones 02/02/17 04/15/17
    Smith 05/29/17
    Williams 01/31/17
    Black 01/08/17 04/22/17
    Jackson 03/17/17

Leave a Reply

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

*