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!
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.
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!
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.
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.
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.
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?
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.
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
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.
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?
@@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.
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.
As with others, I am glad I came across your explanation. Much appreciated.
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!
This solved my problem. Thank you very much!
Scrolled through multiple results, finally stumbled upon your video, thanks a lot, this is the solution I was looking for (with blank cells)
Searching for a solution, found this vid, watched the explanation, CLEVER!!
Thanks!
Thanks so much! This was killing me, but you saved the day!
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.
This video was amazing. You are really good explaining! Thanks.
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!
Hey, your vedio is informative, just want to know how will it work with negative valves
Invaluable formula! Thank you so much!
Thank you so much! It works 🎉
Thanks for your video! by the way, how can I show the latest 5 records of a specific record or name in the list?
I found this very useful! How can i amend the same formula to give the first value. not last ??
Great explanation sir, Thank you
Thank you sir, great video. 😊
Sending you a cold one... Thanks!
Thanks Sir searching many videos, but finally got it👍
Wao Awesome! very informative tutorial Thanks for sharing this tutorial, Sir.
Perfect, just what I was looking for, Thank´s 🙂
Thanx alot brother...it very useful for me...
THANK YOU! Well explained. To the point. Just what I needed.
I just tried using this formula but it is returning the value in the top most cell, not the bottom. Any thoughts?
Amazing ❤❤
Thanks a lot, your video with this solution helped.
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.
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.
is useful for me, thanks for helping and teaching.
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 so much , worked for me
Thanks. Well explained. 👍
Thank you much❤❤❤❤❤
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?
Very helpfull for me....
Thank you once again.
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?
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?
Can you do this with multiple criteria?
Thank you very much sir
Thanks, It Help Me A Lot.
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.
thankyou h]it helped a lot
Is there a way of getting the 2nd last and 3rd last and so on?
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.
I have a similar case where i need to find the value before last one. How to I achieve this?
You are great.
Mr.. How about 4 last sales?
thanks so much
Could you tell me how to look up the value at previous date in a column in excel please !
Brilliant
how should i do it if my data is in a different sheet? I'm having trouble doing this
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
Refer to the column in which price is.
THANK YOU!
Is there way to get the first value using this formula?
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.
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?
NICE
Do you have a way to index the last sale of each employee?
It depends on how your data is organized.
instead of getting the last value, i keep getting the first value. please help
Any idea how to get second last value (or previous value from the current value)? I am out of idea. :(((
How to find the row number for the first occurring specific value??
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?
i want last bob value , can you help me plz??
How to get last cell value date? (As per your example)
Using Lookup function, how we can find out the first value?
Did not find value 2
Thak you
👍👍👍👍👍👍👍👍👍👍👍👍👍
I tried this on google sheets and it said "Did not find value 2 in lookup valuation". Any help? Thank you!
Try using the formula isblank and lookup false =lookup(false,isblank(A1:A100),A1:A100)
@@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.
How to get the column header once you get the last value?
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.
Does not work.