Dear planiolro, Great question! Examples of volatile functions are: OFFSET, INDIRECT, TODAY, NOW, RAND, CELL, INFO. Volatile functions can significantly slow down calculating time because these functions are recalculated every time recalculation is done regardless if the precedents have changed. Smart recalculate will not calculate formulas where the precedents have not changed, but will recalculate volatile functions. --excelisfun
LOOKUP is only programmed to do approximate match, it does vertical lookup if the number of rows are equal to or greater than the number of columns (looks in first column and returns something from the last column), it does horizontal lookup if the number of columns are greater than the number of rows (looks up something in first row and returns something from the last row).
It should work. 9.99999999999999E+307 is the biggest number that Excel understands. 2^15-1 is the maximum number of characters allowed in a cell. As long as you use a number that is sure to be bigger than anything you put in the template, it will work!
How to you determine which is the last? is it in a row, or in a column, or is it the rectangular range defined by the last row and column? Try this video: Excel Magic Trick #135: Find Last Row & Column Dynamic Range
In your example: =Lookup(9.99999999999999E+307, A1:K10) it would try to find the biggest value in the first row and then try to return something from the 10th row.
Thanks for the hot tip, Xavier! Yes, Big Number, 9.999999999999999E+307 is teh way to go. If you use the BigNum a lot, you can save it as s Defined Name.
Hi Mike, Just wanted to add a point to your formula. When you're using the Lookup formula to find the last number in an array, it's recommended to use 'a number that's slightly greater the largest value in that array'. For instance, instead of using '=lookup(max(array), array)' you should use '=lookup(max((array)+1), array)'. This way, the last value of the array where the largest value appears is also correct. Else, for the array with the largest value, it pulls only the largest value.
In Trick#135 you see a method for finding the row and column, then just use those with the INDEX function to retrieve the values. But as I mentioned earlier, I still don't know what last value you want.
Thanks for the hot tip. Your Magic Trick#135 really helped. I was hoping to use a formula to find the last value used in a range or a cell address ( similar to using key board Control + End ).
Dear kayno9, How long does it take me? Always different. Sometimes I know the answer right away, other times it takes a while. In this case, I could think of these 3 solutions & a few more almost instantly. Well that's not really true. I thought of three ways, and then later (10 minutes), I remembered the LOOKUP. And that is usual, you think of a good way, and then later you "all-of-a-sudden" think of another way. I sent a note to Mr Excel asking him about the VBA video series. --excelisfun
Awesome solution with Lookup function.During the vdeo you mentioned the term "volatile function". Which functions are volatile in Excel? What does it mean volatile function?
Hey Mike another great challenge. One quetion. how long does it takes for you to come up with a solution i.e. like this one? Did you know it strait away such as the OFFSET function will work here? Can you please also ask MR excel for me when his Macro video series will come out because i could not find it anywhere on his website like you advised me before. Many times again for your great excel instructional video.
I figured out how to retrieve the second to last value in a row by using =INDEX(rRange,LARGE(1*(rRange"")*COLUMN(rRange)-COLUMN(),2)) CSE but it seems to use a lot of memory. I hope to figure out how to make this better.
I finally found a solution to my problem! However, it's a partial soluition. Now that value is last in the row, if I need to excel to refernce row 1 and return the value corresponding to the cell where the last value is. how do i do that? Thanks!
Hello Sir, I hope you are doing awesome, I am trying to fetch out all the non blank cell values from a single row but unable to get it. Could you please help me with a formula by which i can find and store all the non blank cells value in a single cell from a same row/ single row. Values are like (using row 2) - empty : empty : value : value: empty : value : empty and so on. Please note - these values are available horizontally in my spreadsheet. Please help.. Thanks in advance. Regards, Zahid Shaikh Mumbai, India
hi Mr excel, i had a problem here, i have a data such as GD0001, GD0002, ... until GD1000 in one column . So, how can i pick the last data GD1000 to display in other table. i've been search for a month. pls help...
Both are great sir. But sir I need last value date. You put it estimate 1 or estimate 2. My table is different in place of estimate I have some date. I need the last cell date. Please help. I from India
I tried this one with countifs using 2 conditions and it always gets the first value in the column (using columns in my case) Wish someone can try that. =INDEX(EmpOps!F:F,COUNTIFS(EmpOps!C:C,O16,EmpOps!A:A,L15),0) Basically I have 2 columns to check, if the values are equal to the text i've entered, retrieve the last value in the column F.
Dear planiolro,
Great question!
Examples of volatile functions are:
OFFSET, INDIRECT, TODAY, NOW, RAND, CELL, INFO.
Volatile functions can significantly slow down calculating time because these functions are recalculated every time recalculation is done regardless if the precedents have changed. Smart recalculate will not calculate formulas where the precedents have not changed, but will recalculate volatile functions.
--excelisfun
LOOKUP is only programmed to do approximate match, it does vertical lookup if the number of rows are equal to or greater than the number of columns (looks in first column and returns something from the last column), it does horizontal lookup if the number of columns are greater than the number of rows (looks up something in first row and returns something from the last row).
It should work.
9.99999999999999E+307 is the biggest number that Excel understands.
2^15-1 is the maximum number of characters allowed in a cell.
As long as you use a number that is sure to be bigger than anything you put in the template, it will work!
How to you determine which is the last? is it in a row, or in a column, or is it the rectangular range defined by the last row and column?
Try this video:
Excel Magic Trick #135: Find Last Row & Column Dynamic Range
In your example:
=Lookup(9.99999999999999E+307, A1:K10)
it would try to find the biggest value in the first row and then try to return something from the 10th row.
Thanks for the hot tip, Xavier!
Yes, Big Number, 9.999999999999999E+307 is teh way to go. If you use the BigNum a lot, you can save it as s Defined Name.
Hi Mike,
Just wanted to add a point to your formula. When you're using the Lookup formula to find the last number in an array, it's recommended to use 'a number that's slightly greater the largest value in that array'. For instance, instead of using '=lookup(max(array), array)' you should use '=lookup(max((array)+1), array)'. This way, the last value of the array where the largest value appears is also correct. Else, for the array with the largest value, it pulls only the largest value.
Thank you. The Lookup function was exactly what I was looking for.
I always enjoy the duels!!!
Only 145 likes ?!! Thanks Excelisfun, you saved my day. Cheers bro
mike you are the excel formula king you make my life more fun
because i love excel thank you
In Trick#135 you see a method for finding the row and column, then just use those with the INDEX function to retrieve the values. But as I mentioned earlier, I still don't know what last value you want.
Thanks for the hot tip. Your Magic Trick#135 really helped. I was hoping to use a formula to find the last value used in a range or a cell address ( similar to using key board Control + End ).
Dear kayno9,
How long does it take me? Always different. Sometimes I know the answer right away, other times it takes a while. In this case, I could think of these 3 solutions & a few more almost instantly. Well that's not really true. I thought of three ways, and then later (10 minutes), I remembered the LOOKUP. And that is usual, you think of a good way, and then later you "all-of-a-sudden" think of another way.
I sent a note to Mr Excel asking him about the VBA video series.
--excelisfun
Awesome solution with Lookup function.During the vdeo you mentioned the term "volatile function". Which functions are volatile in Excel? What does it mean volatile function?
Hey Mike another great challenge. One quetion. how long does it takes for you to come up with a solution i.e. like this one? Did you know it strait away such as the OFFSET function will work here?
Can you please also ask MR excel for me when his Macro video series will come out because i could not find it anywhere on his website like you advised me before. Many times again for your great excel instructional video.
Dear planiolro,
Oh ya, Conditional Formatting with TRUE FALSE formula is also volatile
--excelisfun
Hi. Thanks for this video. How would I use the index function if my "row" was dependent on a VLOOKUP?
Very helpful. Thank you both!
Cool, Thanks!
Hi, Could you please tell me if u have empty value in middle in row, then this formula not work. How to handle this?
I figured out how to retrieve the second to last value in a row by using =INDEX(rRange,LARGE(1*(rRange"")*COLUMN(rRange)-COLUMN(),2)) CSE but it seems to use a lot of memory. I hope to figure out how to make this better.
I finally found a solution to my problem! However, it's a partial soluition.
Now that value is last in the row, if I need to excel to refernce row 1 and return the value corresponding to the cell where the last value is. how do i do that? Thanks!
Hi, Index function is not working, if there are blank cells in row
Any formula to find the second last value in a row, please advise. thank you
=LARGE(A:A,2) should do the trick
Is there a way to return the address of the last value in a row?
HI! what if it's not a numbers? i want to get the last row text. how do I do that?
Thanks Excellsfun
Hello Sir,
I hope you are doing awesome,
I am trying to fetch out all the non blank cell values from a single row but unable to get it.
Could you please help me with a formula by which i can find and store all the non blank cells value in a single cell from a same row/ single row.
Values are like (using row 2) - empty : empty : value : value: empty : value : empty and so on.
Please note - these values are available horizontally in my spreadsheet.
Please help..
Thanks in advance.
Regards,
Zahid Shaikh
Mumbai, India
hi Mr excel, i had a problem here, i have a data such as GD0001, GD0002, ... until GD1000 in one column . So, how can i pick the last data GD1000 to display in other table. i've been search for a month. pls help...
this video is the answer for my pray :)
Post question with details to:
mrexcel[dot]com/forum
thanks Mike
Both are great sir. But sir I need last value date. You put it estimate 1 or estimate 2. My table is different in place of estimate I have some date. I need the last cell date. Please help. I from India
Namste bhai
kal mile teach to each channel me
great
I need a formuls to pull in the last entry in a row, this is not working for me..... HELP
How to get value last to 2nd
I tried this one with countifs using 2 conditions and it always gets the first value in the column (using columns in my case) Wish someone can try that. =INDEX(EmpOps!F:F,COUNTIFS(EmpOps!C:C,O16,EmpOps!A:A,L15),0)
Basically I have 2 columns to check, if the values are equal to the text i've entered, retrieve the last value in the column F.
my cell content is not a number... so