Get the Last Value in a Row - Excel Formula

[@magicassassin1947]

Wowwwwwww 💓💓💓💓😍😍 thank you💓💓😍

[@Computergaga]

Welcome 😊

[@lynxwomancat]

Genius!! Just solved an issue that will save me a lot of work!!! I no longer have to change the formula, each month, to the newest populated column. Virtual fist bump, Alan!

[@Computergaga]

Thanks Sandy.

[@geoffh1055]

Always great to watch, very helpful instructive and well explained. A definite 'go to' resource

[@Computergaga]

Thank you Geoff 👍😊

[@wayneedmondson1065]

Hi Alan.. INDEX, MATCH and COUNTA to the rescue. Excellent! Your clever examples and solutions are teaching my brain how to solve many problems. Thanks and Thumbs up!

[@Computergaga]

Thank you Wayne.

[@bramstebbings1948]

@computergaga Thanks for what you do! One thing about google sheets... for any array formulas like this you must add the array formula function. then you're off to the races.

[@Computergaga]

Thanks, Bram.

[@brauliogarcia1836]

I have been looking for this solution for a long time, thank you!

[@Computergaga]

You're welcome, Braulio.

[@mestergabbe]

Thank you! Im new with google sheets/excel. Been struggling with this formula, finally i have it

[@Computergaga]

You are welcome, Gabriel.

[@SyedMuzammilMahasanShahi]

Beautiful work. Thanks for sharing

[@Computergaga]

You're very welcome Syed.

[@tej9612]

It works. God bless you. Thanks 🙏

[@Computergaga]

You're welcome!

[@buddhikaeranda2246]

Really really helpful ...!good job!

[@Computergaga]

Thank you, Buddhika.

[@miguelmachado5614]

Works like a charm! thanks

[@Computergaga]

You're welcome!

[@houssamHi]

Great description sir, you should get 10 likes

[@Computergaga]

Thank you very much 👍

[@haroldsalesvillamor5657]

verrryyyy helpful, thank you :)

[@Computergaga]

You're welcome 👍

[@beccachavez9328]

Thank you so much for this video!!

[@Computergaga]

No problem 😊

[@catcat2508]

Very good tutorial👍🏻

[@Computergaga]

Thank you Phyllis.

[@appahwoyengikuro3228]

Just what i needed right now

[@Computergaga]

Happy to help.

[@dbcooper488]

I'm a big fan of you after watching this video

[@Computergaga]

Great! Thank you.

[@dbcooper488]

@@Computergaga Hello Can you help on this Where do I see to copy MS Excel file path ???? Normally when right button click on mouse it will see but It not showing... Please help

[@rodrigomachado3678]

This is great, thank you!

[@Computergaga]

You're welcome, Rodrigo.

[@kaeaek6143]

I got it! Thank you!

[@Computergaga]

You're welcome 👍

[@sarathganesh]

Hi! A very crisp video. Thanks for taking the time. Can you shed a little insight on how to use this for data that are in a single column? Fetch the last entry in column D, for example. I tried =Index(Range),1,0 and many variants to get it to work but with no luck

[@THEGLADIUS101]

Hey Allan what is the logic of counta in the index column section that lets you capture the last value?

[@Computergaga]

The COUNTA function counts all no empty cells. So here it counted the values in row 2 returning 6 which is the last used column i.e. current week.

[@ericapearl04]

Great example! But what if I am looking for text comments entered & not numbers?

[@Computergaga]

Thank you! The COUNTA function will count all non-blank cells, so text values too 😊

[@mansourzanaty691]

Thank you so much.

[@Computergaga]

You're welcome, Mansour.

[@ruthrobinson1518]

This has worked well for me, thank you. Only problem is I have two cells that are coming up blank - nothing in them. They have exactly the same calculation as all the others.

[@tahirnazir2704]

Very useful video. What if we have to choose column header of last value's of row instead of its own value. Please help with formula for the same, if you could...

[@Computergaga]

You're welcome. For the last header value using this data. The formula can be: =INDEX(A2:NZ2,,COUNTA(A2:NZ2)) This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.

[@sourabh5461]

Hi Allan, Good Job But I have one query if I want above mentioned Date instead of Numbers which formula is suitable can you please share Formula..

[@Computergaga]

Change the row number in the first range of the INDEX function to use row 2 instead of row 3. Everything else stays the same.

[@PerPettersson]

Brilliant explanation. Thank you!

[@Computergaga]

Thank you Per.

[@minhthai2804]

Hi, how about if there is any empty cells in between the columns, this can't be work at all

[@Computergaga]

True, so we would use a slightly different approach. We could count the blanks and add them. Or format the range as a table and use the COLUMNS function with it.

[@minhthai2804]

@@Computergaga thanks

[@kailinn3646]

Hi Allan, thank you so much. I was wondering if I want the date of the last occurring numbers how do I change the formula? Because some of the date will not have data in it.

[@Computergaga]

Sure. Just edit the ranges used in the INDEX and COUNTA functions to look along the rows of dates and not the values.

[@UrosDavidovic25]

Very helpful! My case is a little different, but it should work, I have a string of values in 8 columns of a row, and need to figure out the last value that is not a number and not 0. So, essentially last number > 0. I am guessing the COUNT criteria just changes.

[@nidhikadam7173]

Hi! Great video and very helpful. Just the solution I was looking for. It worked on most of my data but in a few rows, it did not pick the last value and instead displayed a zero. How can I correct this, any suggestions?

[@osamasherwani2354]

Thanks for the sharing, can you please let me know if there is 0 in data & we need to find data instead 0, what would be the formula?

[@airtimebill]

I need this too??? Help

[@kanishkadeshpande2213]

Can you please suggest same for Date? I am in need

[@Computergaga]

Depends more specifically on what you are asking, Kanishka. The technique in the video can return dates, if that is what you are asking.

[@arzumehrali9083]

Thank you! it is helpful

[@Computergaga]

You're welcome, Arzu.

[@MichaelBurnsGuitar]

I tried the first one in a column, returned #REF, time for more troubleshooting!

[@Computergaga]

👍

[@ibrahemjamil1004]

Just one quotation before counta

[@TwinTwo602]

Is there a way to get the date of the last value in that column?

[@Computergaga]

For the last header value using this data. The formula can be: =INDEX(A2:NZ2,,COUNTA(A2:NZ2)) This would be a more effective formula if a table was used instead of ranges. We then would not need to reference column NZ.

[@Whitegatellc1]

Alan, this video was really helpful. What if I want the column header (Date in your example) returned for the last, non-blank cell in each row? Do you know how I can setup the function? Thanks for your anticipated assistance.

[@Computergaga]

Great! You can use the same formula, Abimbola, but reference C2:CZ2 instead.

[@ayoubyaacoubi2437]

Brilliant thank u so much

[@Computergaga]

You're welcome. Thank you Ayoub.

[@nielsnielsen2708]

yes okay, the formula is working now. thanks.

[@Computergaga]

Excellent!

[@oananegrea6110]

What if the cell before the last value is blank? Why the last value is not showing when the cell before is empty ? Can you help me with this problem please ??

[@XxX-nt7yn]

What if the data is '0012346 in cell GE Text format?

[@Computergaga]

The COUNTA function counts all non-blank cells, so will include numbers, text, and errors. Alternatively though, the COLUMNS function can be used. This function returns the total number of columns in a given range, table, or array.

[@romany4all]

Great .. go on

[@AsiriRodrigoAccounting]

Thanks

[@Computergaga]

You're welcome Asiri.

[@Victoria-mz6dr]

Is there a way to do this in Power Query?

[@Computergaga]

I'm positive there is, but I'm not sure of the approach to write here. Sounds like a nice challenge Victoria.

[@tusharnarke5358]

Thanks for the video...

[@Computergaga]

You're very welcome Tushar.

[@wodezhu]

great video! was really helpful. i used the index formula to pull data for calculation. One question, how can i keep the data thats being pull for calculation to not changed when new data are being added to the table? thank you.

[@Computergaga]

Thank you. To stop it from updating when new data is added, you could to remove the formula. Select the cell(s), copy and paste values.

[@wodezhu]

Computergaga thank you for the reply. This is what we are doing now. Was hoping it can be automated to reduce human error. ☺️

[@Computergaga]

Well the formula is automated to always return the last row even when the table expands. But if you don't want this you can remove the formula. Other options could be Power Query or a macro.

[@ricardolimpias]

Thanks!

[@Computergaga]

You're welcome Ricardo.

[@jennifergodreau5235]

This is very helpful!! Question: How would you handle roll over numbers using these formulas? For example, let say Product E has rolled over after reaching 1000, thus your value for week 22/11/2018 is 153 instead of 1153. Now, you want to calculate the increase between this week and last week. How would you account for this in your formula? I'm doing something similar for meter readings and they roll over at a certain point. Thank in advance for any recommendations you can offer:)

[@rickkeev4919]

Great video, but if there are blanks in the data it gums it up a bit.

[@Computergaga]

For sure. Blanks can be naughty in Excel. Using the header row for the count is more reliable, unless you have a blank column.

[@vandalo7494]

You could use the following formula to find the last value in one row =index(yourvaluecells,,match(9.9e307,yourvaluecells,1))

[@osamasherwani2354]

@@Computergaga pls share formula

[@carlosubeda6466]

Thanks for the video, very helpful. Only problem I am having is that some cells in my row are empty, so it doesn't include them in the counta function, which means the counta value is less than it should be. Any way to overcome this?

[@Computergaga]

Thank you Carlos. you could run the COUNTBLANK function also and add them together.

[@stephanrodriguez6587]

@@Computergaga Thank you so much! I spent hours trying to find this resolution!

[@htcsilvio]

How i can get last value in a row when is text format? Thanks

[@Computergaga]

This technique will work with text also Silvio.

[@htcsilvio]

@@Computergaga With text, this not work for me. =INDEKS(B3:NE3,1,COUNTA(B3:NE3)) not work

[@Computergaga]

Maybe you have blank cells in the range. COUNTA will count all non blank cells from B3:NE3.

[@htcsilvio]

@@Computergaga yes i have blank cells... have any other formula for my problem?

[@Computergaga]

There is a function named COUNTBLANK. To find the last row, you need to add the COUNTA and COUNTBLANK results together.

[@pipo441]

This only works when you have the column before the data range..... correct?

[@Computergaga]

No, you can return the result to anywhere.

[@pkentertainment1979]

What if the row has an empty cells in between the values

[@Computergaga]

That would present a problem. Empty cells are rarely helpful. The best solution would be for this range to be formatted a table. Then COLUMNS can be used instead of COUNTA.

[@nielsnielsen2708]

When i put in the formula=index(b4:k4,1,counta(b4:k4))a vindow pops up and tell me that there is a failure somewhere!why will it not work for me?

[@Computergaga]

Could be that you need semi colons instead of commas =INDEX(B4:K4;1;COUNTA(B4:K4))

[@nielsnielsen2708]

I have another problem, if you have the time for it. I like to retrieve values from 14 different cells in a row over a period of 14 days into one cell. Each day i put in a value in a new cell(yellow) and this new value shall replace the value from the day before and presented in the same cell(blue).The blanks cells between the yellows is formulars and can not be changed. I can not figure out to create this formula. Is there a solution for this? A1 is blue. B1, C1, E1, G1, I1, K1, M1, O1, Q1, S1, U1, W1, Y1, AA1 is all yellow

[@Computergaga]

I'm not exactly sure what you mean. It sounds like this video is exactly what you want. Retrieving the last value in the row of yellows and putting it in the blue cell.

[@airtimebill]

Thank you for sharing. Loved the tutorial and I thought I found a solution to my problem; however, the formula is not working? It is just showing #REF! and not the last value? I tried semi colon as suggested below? that didn't work for me. I have used a name array (ID1_BEST5) in a motorsports points table which highlights the total points at the end of each rd (total of 10 rds in the championship) not highlighting ever race that makes up a round (e.g. 3 races = 1 rd), so ID1_BEST5 cells are H9, L9, P9, T9 and so on. I want/need to display each riders last points they scored for a round. Here is the formula I used from your tutorial =INDEX(ID1_BEST5,1,COUNTA(ID1_BEST5)) No idea why this is not working because your lesson made so much sense. If I copy (and link the cells) from each rd total and put them next to each other and/or name the array with a new name, it works, but this seems like a lot of extra steps? Thank you, I have been reading the net for hours/days trying to find a solution.

[@Computergaga]

Thank you Bill. I cannot tell from your formula what the problem might be though.

[@janjrukiyasagar2357]

I want do following this how can I enter formula for this 1) When C1 is equal to D1 then store D1 value in next sheet/ in this sheet 2) again when C1 is equal to D1 then store D1 value in next sheet/ in this sheet below above value Give me the solution of this

[@hrexecutivemarquee]

SALUTE TO YOU 😻

[@Computergaga]

😊

[@olson7117]

how about last value in a row

[@Computergaga]

That is what the video is showing. The same tech pique can be applied to a column though. Just adjust the ranges.

[@davidraley3239]

Great video but you made one mistake in calculating the difference %. Formula should be (Current-Prior)/ Prior. In this case (636-527)/527 or 20.7% difference.

[@Computergaga]

Good point David. Well spotted. Thank you for your comments on the video.

[@happy1382]

getting a 0 value..:(

[@bq.]

TLDR: =index(range:range,1,counta(range))

[@Sandeesh_Srikantam]

Wats that ?