Lookup the Last Value in a Column or Row in Excel | Get the Value in the Last Non-Empty Cell
Тәжірибелік нұсқаулар және стиль
Download the featured file here: www.bluepecantraining.com/wp-...
In this video I demonstrate how to lookup the value in the last non-empty cell in a row or column in Excel. This can be achieved using the LOOKUP function.
------------------------
Пікірлер: 72
Searching for a solution, found this vid, watched the explanation, CLEVER!! Thanks!
As with others, I am glad I came across your explanation. Much appreciated.
Thanks so much! This was killing me, but you saved the day!
Scrolled through multiple results, finally stumbled upon your video, thanks a lot, this is the solution I was looking for (with blank cells)
This video was amazing. You are really good explaining! Thanks.
THANK YOU! Well explained. To the point. Just what I needed.
Invaluable formula! Thank you so much!
I just tried using this formula but it is returning the value in the top most cell, not the bottom. Any thoughts?
Perfect, just what I was looking for, Thank´s 🙂
Thanks. Well explained. 👍
Very good and helpful video; I wonder; though I may never use it if using the row can be inclusive as is the column i.e., (C:C) If so, what would be the syntax?
Thanks Sir searching many videos, but finally got it👍
Thanks, It Help Me A Lot.
is useful for me, thanks for helping and teaching.
Thank you so much , worked for me
Wao Awesome! very informative tutorial Thanks for sharing this tutorial, Sir.
Thanks a lot, your video with this solution helped.
Sir, I have a query i.e. i.e. in a row few cells have some value and few cells have zero value but if I use the formula shown in the video, the answer I am getting is zero as the last cell has the zero value. Kindly help me.
Thank you sir, great video. 😊
Great explanation sir, Thank you
Thank you once again.
Hey, your vedio is informative, just want to know how will it work with negative valves
Thank you very much! I had a persistent problem in which my formulas were correct, but excel was not accepting them. It placed a red border around my cell. For anyone experiencing this, know that it has to do with your regional settings. All you have to do is change " , " to " ; " in your formula. Hope you won't have to look for hours like me :) Good Luck!
@DomHC4L
Жыл бұрын
This solved my problem. Thank you very much!
Thanks for your video! by the way, how can I show the latest 5 records of a specific record or name in the list?
Thanks Chester. That's a very useful video. Now how would you, in your example, return the last value for only columns that have "Bob" as a header?
Thanx alot brother...it very useful for me...
Thank you . I tried searching google for the answer to my problem and tried their suggestions but they didnt work. I looked at your video and my problem was solved instantly and very clearly.
I found this very useful! How can i amend the same formula to give the first value. not last ??
thankyou h]it helped a lot
You are great.
Thank you for a clear and concise explanation of the LOOKUP function and its use. It will be useful to have a video to show how you might LOOKUP the last value of a column/row from a different sheet within the same workbook (or even a different workbook). I needed this in my budget workbook so that I can start for example the month of February my February worksheet with the balance from the last entry in the January worksheet. =LOOKUP(2,1/('Jan 22'!E3:'Jan 22'!E25""),'Jan 22'!E25:'Jan 22'!E25). In position E3 in the February worksheet type this formula, and it will LOOKUP the last entry in worksheet Jan 22 in Column E from row 3 to 25. I only have 21 entries and allowed 4 more for later additions. Should you exceed 25 rows in your January worksheet, please remember to modify the number 25 in location E3 in the February worksheet to a number past your last row that includes your January balance. To use a location in a different workbook you will need to include the path to that workbook in the LOOKUP formula.
THANK YOU!
thanks so much
NICE
How to find the row number for the first occurring specific value??
Very helpfull for me....
Thank you for this. I am looking for this option but slightly different. I am looking for the 2nd last occurrence of data in a row. For example in Cell F5, I return the last data entry in that row by using the formula =LOOKUP(2,1/(5:5""),5:5). In Cell E5, I want to return the 2nd to last data entry in that row but can't figure out the best formula to do that or how to modify.
@Jump3RPictur3s
Жыл бұрын
hi Robert. if you do it for limited range of the lookup instead of entire row/column it's doable. technically it can be done for the entire row or column but you would need to take into consideration some error handling for when there's only 1 value (i.e. no 2nd last occurrence available at all)... you can simply get what you want like this though: =LOOKUP(2,1/(B6:XFD6""),A6:XFC6) you just shift the results by offset you want (1 column/row for 2nd to last occurrence, 2 columns/rows for 3rd to last occurrence, etc.) basically offset the result by N columns/rows to return N+1 last occurrence
I need this forumla to ignore when its poulated as N/A Say Jan 1 and Jan 30 are set columnes populated by a vlookup its Jan 15, so the 16-30 is just N/A I need my lookup to populate the 15th cell and not the N/A cell on the 30th any ideas?
Thank you much❤❤❤❤❤
The column I am using this formula on has formulas in the cells. Is there something that works if the column cells have formula in it?
how should i do it if my data is in a different sheet? I'm having trouble doing this
good video, it gives me idea. But what I want to understand is how to get the last sale of the specific person. e.g. Bob.
Could you tell me how to look up the value at previous date in a column in excel please !
Brilliant
Thank you. This is exactly what I needed explained because I was tired of reading unclear explanations and my self testing failing. This was clear and easy to follow. Well done! Thank you!
What happens if I have an array that is like this [1,1,1,"", "", 1,1,1, "", ""]. How do I find the position of the last cell that is 1?
Is there a way of getting the 2nd last and 3rd last and so on?
instead of getting the last value, i keep getting the first value. please help
I have a similar case where i need to find the value before last one. How to I achieve this?
Can you do this with multiple criteria?
Is there way to get the first value using this formula?
Any idea how to get second last value (or previous value from the current value)? I am out of idea. :(((
Did not find value 2
i want last bob value , can you help me plz??
Thank you for this video, This formula works great, except for when there is an actual 0 in the data, then it takes 0 as the last value in the column instead of the last non-zero value before the zero. How can I account for this? And can this be changed to return the first non-zero value in a column? Thank you again, great video.
@voiceofsoul7865
Жыл бұрын
The same query I have i.e. in a row my cells have some value and some cells have zero value but if I use the formula shown in the video, the answer I am getting is zero as the last cell has zero value. Kindly help me.
Do you have a way to index the last sale of each employee?
@mdca8455
2 жыл бұрын
It depends on how your data is organized.
Mr.. How about 4 last sales?
cam we use a lookup function in a non sequential cells for example find the last price of the product , when your data is in such a way that (quantity, price, value),(quantity, price, value),.... & these values are in a row
@mdca8455
2 жыл бұрын
Refer to the column in which price is.
Using Lookup function, how we can find out the first value?
How to get last cell value date? (As per your example)
How to get the column header once you get the last value?
@allang9313
2 жыл бұрын
Using Chester's first formula as the example, change the second instance of C4:C20 to A4:A20. So, the formula changes from being: =LOOKUP(2,1/(C4:C20""),C4:C20), to =LOOKUP(2,1/(C4:C20""),A4:A20) Remembering to format the cell that you are entering the formula into as a date.
I tried this on google sheets and it said "Did not find value 2 in lookup valuation". Any help? Thank you!
@danielcheatham1537
2 жыл бұрын
Try using the formula isblank and lookup false =lookup(false,isblank(A1:A100),A1:A100)
@TheHadMatters
Жыл бұрын
@@danielcheatham1537 Both your version and the version in the video work in Excel. They provide exactly the same result, including checking for cells filled with non-numerical text. But neither of them work in Google Sheets. They return the topmost value of the list instead. I'm sure it's because of differences in rounding behaviour or how the program solves divisions by zero.
Does not work.