Excel Magic Trick 977: Lookup First Non Empty Cell In A Range, Lookup Last Number In A Range

[@excelisfun]

Yes, the current class really rocks and it is soooo much fun to hang out "live" in a room full of Excelers.

[@avijitnov]

You saved my day!!

[@excelisfun]

Yes, that will work! If you had a large spreadsheet and calculation time was a factor, using a hard coded number would help to calculate faster.

[@ExcelStrategy]

After some tests I think that the most elegant one is to obtain the array using the "index" with the "Isblank" and the "zero column" trick, then convert the bounch of true and false in a bounch of one and zeros putting the "double negative" in front of it, then match the position of the first zero inside this array using "zero" for the argument of the "match", and that shuld give to the external "index" the position of the first "non empty" cell ! Hope my description is understandable :)))))

[@hugostein8092]

Thx man you saved my day,.... Actually you saved my entire investment analysis project

[@SaiGuy_]

Thank you for the carry

[@excelisfun]

You are welcome!

[@krn14242]

Great job Mike. Smart class you have. Kudos to Joyce and April.

[@nanoak85]

Very useful post, Mike. What if we want to show first the non-blankcell, and later the second non-blank value, and the third one...? Like deleting the empty lines...I think about a conditional function, where the array changes with the location of the previous found value. For example, if your first value is found in row 4, the next array will go from 5 to 10 and not from 1 to 10. I would appreciate some help. Thanks

[@excelisfun]

MAX is not bad; in fact it is a common cool trick!

[@excelisfun]

Cool! That is a great idea!! If you download the Excel workbook or look in the video I have placed such a formula in cell E8 and wrote "No CSE" next to it. Do you have a preference about which you like better: 1) FALSE and ISBLANK 2) TRUE and E2:L2 not ""

[@mohabhamad5094]

actually I need to get the header of last filled cell whether txt or nos , same example you have mentioned I need to get day # for the last filled cell dynamically , so always get the day number whenever coping the formula down.

[@Jaba01]

You are right! I didn't pay attention about the computational time. My bad :) I totally agree with you. Thx for the explanation and congrats for the great work!

[@katerina6495]

Fantastic Mike, fantastic, this trick is amazing, you always say more in your videos

[@excelisfun]

Glad it is fantastic! Thanks for your amazing support : )

[@querrythis]

Very helpful. Although syntax is fine, for me the ISBLANK is more explicit.

[@AnnHsu-md5pl]

That's excellent tips. I have a scenario where I need to look up the header info (in your example above = Day 1 or Day 3). How can I do that?

[@pranoysingha3236]

This is really useful. Using this, we can find the first cell location containing data, but is there any way to find the last cell location containing data?

[@ExcelStrategy]

Becouse i think that this "MATCH(0;INDEX(--ISBLANK("range");1;0);0)" version of the formula it is very elegant :))). In one of my experiment I have subtracted the "Isblank" from 1, converting every "True" to "zero" and every "false" to "one", and in that case it is possible to use "1" for the argument of the match, but at the end i think the double negative its mouch more elegant :))).

[@williamamankwah6667]

Nice one. But please what about if you want to find a last but one non zero cell in a range of a column? How can I go about it? last but one non-zero cell. please help me.

[@excelisfun]

I am not sure that I follow. Why do you have to convert to 1s and 0s?

[@excelisfun]

Fun is good!!

[@dimitriospapapavlou9539]

This is great, but what if you need to search in specific columns ? Only in Day 1, Day 4, Day 6 etc. ? A reply would be super appreciated. I only need it to search in specific columns, not the whole line

[@abhinavkadam8579]

Hi Mike, How can be formula further be extended to pick header of first filled cell??

[@matthewhopkins475]

Going one step further, how do you think this could be used to copy data from a master entry sheet into a dynamic sub table, matching a criteria, into the first available blank cell? Currently stuck on this problem myself and it's been a real pain trying to figure it out!!!

[@pankajkumaram]

Thank you so much Sir.

[@RobertoRomeroJr]

Hi. How can I extract data for the next non-zero and non-blank cell after the first value? I hope I can be helped on this one. Thank you!

[@ExcelStrategy]

Hello Mike, maybe it is possible to avoid the CTRL+SHIFT combination putting the "isblank" inside an "index" function with the row number set on 1 and column number set on zero, it should retourn an array inside the match without CTRL+SHIFT .

[@hsab5927]

awesome thanks

[@excelisfun]

Sure, post a video response.

[@mcrams1]

Mike, Is it possible to submit questions for solutions or projects as a video? Thanks.

[@paulbaird1351]

Is it possible to get the arrange to look from right to left? Or more specifically for my need to look from down to up if using in a column?

[@manishjain4540]

thanx alot

[@stephenbrincat7124]

Hi, Mike, what if I have text instead of numbers and would need to get the first cell which contains text?

[@MKapitan]

Thanks

[@youneskh8771]

Hollo, Plz what if I want to find the second or third value. Cause I have a customer's which they have different repurchase so I need to figure out first sales /second /third ...)

[@timjacobs2487]

There HAS to be a way to do this... for monthly reports, what is the code to make excel stop counting at the first blank cell? Ex., column C for August has 90 entries, how do I make Excel automatically stop at 91? I have to do this manually for like each month and a ton of columns

[@Jaba01]

Instead of 9.99E+307 I would have used MAX(E2:L2)+1. So you don't need to know the biggest number in Excel and still be sure to use a big enough number. This should work also, right?

[@franciscojrlarita441]

what if the data range have subtotal, how to exclude so that I can get the last number

[@welovespeadsheet]

Sir, in row no 5, if i want second no i.e 12.5 than how can i do?

[@hzaphry]

Ctrl+Shift+Enter that's what was missing, I had the logic many times but never worked. Thanks man. Is there anyway to return values other than the first non-blank?

[@excelisfun]

I am sure there is. What is the logic of what the other is?

[@hzaphry]

ExcelIsFun second third or other. But I got it already using small function . Thanks so much

[@excelisfun]

@@hzaphry Cool : )

[@YY7788JJ]

How can I go back to the last cell position using an already established shortcut or by making a new one in Mac Excel 2011

[@Movie_mania786]

Why we will use 9.99E+307 in lookup value

[@excelisfun]

Why do you need to convert the TRUEs and FALSEs to 1s and 0s? Can't you just skip that and use a lookup value of TRUE or FALSE? Like: =INDEX(range,MATCH(FALSE,INDEX(ISBLANK(range),),0))

[@zemlyaru1977]

What if the last 2 or 3 values are duplicates?

[@kazimshaikh3891]

Is it possible to lookup an alpha numeric value

[@snehamohite2358]

How to fetch the last non 0 value?

[@katwilat]

Great, thanks a lot!

[@startsmall9500]

Good Job :) Thank You!

[@Adin7785]

Awesome! Thank you!!

[@sourabh5461]

Good video, but I Want Date 1, Date 2 etc. Instead of Numbers..Please reply..

[@mohabhamad5094]

how to do the same for text entries?

[@ExcelStrategy]

Becouse it is fun :))))

[@welovespeadsheet]

Please help sir.