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.
Can anyone explain this formula? Whats with adding 307 to the exponent?
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.
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
Try array (ctrl+shift+enter)
=INDEX(D2:H2,MATCH(TRUE,D2:H2<>“”,0))
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
“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.
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!
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.
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
Hi Tom,
This is great, very helpful. When I copy and paste the formula to find the last value a popup comes up saying “There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly.”
What does this mean and how can I solve it?
Thanks
Hello Tara – –
You did not say with which of the two formulas you are getting the circular reference errors.
Is it by chance the second formula to find the last value in a row?
If so, are you entering that formula (let’s say row 2 per the example) in a cell where either there are no other numbers at the moment you enter the formula, or are you entering the formula in a cell on row 2 to the right of all the other existing numbers on row 2. Either of those scenarios would cause a circular ref.
Hi Tom,
Thank you for this tutorial!
I am getting the same error message as Tara but with the first formula (for returning the leftmost/first value in a row). Where should I begin troubleshooting this? I have already confirmed that the row contains values to the right of the cell where I am entering the formula.
Hello Ari.
If you are getting the same message as Tara then you would be entering the formula into a cell that is included in the range of cells where the numbers are that your formula is evaluating. That would result in a circular reference error. Without seeing your spreadsheet and what data you have in what cells, and in what cell you entered the formula that is causing the message, I cannot say for sure but it sounds like a circular reference. Take a close look at the range of cells your formula is actually evaluation, and the cell your formula resides in, and see if that is the problem. Otherwise please explain or post a screen shot of the relevant area of your worksheet and we can take it from there. Sounds fixable.
11 10 9 8 7 6 5 4 3 2 1
6 5 7 4 8 3 9 2 10 1 11
3 9 8 2 4 10 7 1 5 11 6
10 7 4 1 2 5 8 11 9 6 3
5 8 2 11 1 9 4 6 7 3 10
9 4 1 6 11 7 2 3 8 10 5
7 2 11 3 6 8 1 10 4 5 9
8 1 6 10 3 4 11 5 2 9 7
4 11 3 5 10 2 6 9 1 7 8
2 6 10 9 5 1 3 7 11 8 4
1 3 5 7 9 11 10 8 6 4 2
11 10 9 8 7 6 5 4 3 2 1
i need this result.
What is your question?
You posted a bunch of numbers without any context of where they came from, where they are on a worksheet, whether these are your results that you say you need, or if this is your source data and you want other results that you did not post and/or the logic of why you expect what you expect the results to be.
Good Morning, I’m getting a job that requires me to be very adeot with excel… I would like to know the formaula to add the first and last valve of a column, Thanks
Assuming column A and also assuming the first number is visibly evident in cell A2, try
=SUM(A2,LOOKUP(9.99999999999999E+307,A:A))
Hi. Thanks for this formula and the awesome site!
I’m using this formula for column “N” as shown at this picture.
https://photos.app.goo.gl/9uAa3UGy1299iS3z9
Now I have to find the most left value, get the number when it occurred (presented at first row), and place this number at column “O”.
Is there a way to do that?
Thank you in advance.
I’m not quite sure what you mean by:
“Now I have to find the most left value, get the number when it occurred (presented at first row), and place this number at column “O”.”
Can you say what an expected result is for the scenario you describe, which would help me understand what you’re asking about.
Hi Tom,
Thanks so much for providing this service! My question is: I am having trouble perfecting the formula that allows me to find and reference a cell at the end of a data set.
In this example, I am looking for a formula that would recognize 515 as the last number and give me “Last in row” as the result. In my worksheet it’s generally dates on the top row. I am looking to ultimately find the date that matches up with the final value.
I hope this makes sense!
Hello Greg – –
Using column A as an example…
This formula tells you the last numeric value:
=LOOKUP(9.99999999999999E+307,A:A)This formula tells you the row of the last numeric value:
=MATCH(9.99999999999999E+307,A:A)Perfect. I have those down. How would I combine them to give me a specific cell value corresponding to the last numeric value?
(e.g. Columns list days of the week and last number in a row is 0.07% corresponding to Monday. I am looking for a combo that will recognize 0.07% as the last numeric, and return “Monday” in the cell. The days continue to cycle due to other sets continuing on to later dates)
For a formula to do what you want, you would need to first explain how 0.07% corresponds to Monday and how any other number corresponds to any other day.
It is just a simple extrapolation explaining the final percentage date (i.e. what was the date on the final reading of 0.07%?)
Since I have no idea what your extrapolation is, I don’t know how to give you, as you said, “a specific cell value corresponding to the last numeric value”. Try asking at https://www.mrexcel.com/board/forums/excel-questions.10/ but they will ask you the same things I am in order to help you further.
Thank you for your great work! My question:
I have a table of golfers. Names(rows 1-50, FName & LName in Col A & B), with scores by date (Col D to infinity, newest date inserted as Col D each week). Col C would be the value I seek, being the average score for the most recent (leftmost) 8 weeks. Many blanks, as no one golfs every week.
I’m looking for a formula that will take the 8 most recent (leftmost, starting with Col D) values for each golfer, and average them into Col C. Can do helper columns and pivot tables if that works. Any ideas?
Thank you so much.
Thanks for your comment.
Try this in column C and copy down, if I correctly understand what you are doing:
=AVERAGE(INDIRECT("D"&ROW()&":K"&ROW()))Hi Tom,
Thanks very much for this post! One question I had is how I can tweak these formulas so that I can lookup a value vertically based on the first/last cell in a row that has data. Let me explain: imagine row 1 is filled with dates. I want to find the date associated with the leftmost and the rightmost values within each row. So it requires an extra step; once the formula finds the first and last values within each row, it needs to output the date on row 1 associated with that value. This would also need to work for many different rows of data while being “locked” to row 1 in terms of output values.
Anything you can provide here would be most helpful!
Thanks so much
Apologies for the delayed response.
I read your question a few times and I still don’t fully understand it.
Where exactly would you want the output of the corresponding date to be?
What does “while being “locked” to row 1 in terms of output values.” mean?