Excel Magic Trick 977: Lookup First Non Empty Cell In A Range, Lookup Last Number In A Range
HTML-код
- Опубликовано: 5 окт 2024
- Download Excel Start File: people.highlin...
Download Excel File: people.highline...
Two Formulas for Lookup First Non Empty Cell:
1. {=INDEX(E2:L2,MATCH(TRUE,E2:L2 not "",0))}
2. {=INDEX(E2:L2,MATCH(FALSE,ISBLANK(E2:L2),0))}
Formula to lookup last number:
3. =LOOKUP(9.99E+307,E2:L2)
Busn 214 Joyce April Method
![OsamaSon - ik what you did last summer [Official Music Video]](http://i.ytimg.com/vi/4DIQDnu3mr4/mqdefault.jpg)
Yes, the current class really rocks and it is soooo much fun to hang out "live" in a room full of Excelers.
Great job Mike. Smart class you have. Kudos to Joyce and April.
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.
You saved my day!!
Thx man you saved my day,.... Actually you saved my entire investment analysis project
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 :)))))
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
Fantastic Mike, fantastic, this trick is amazing, you always say more in your videos
Glad it is fantastic! Thanks for your amazing support : )
MAX is not bad; in fact it is a common cool trick!
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.
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!
Very helpful.
Although syntax is fine, for me the ISBLANK is more explicit.
Thank you for the carry
You are welcome!
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?
I am not sure that I follow. Why do you have to convert to 1s and 0s?
Fun is good!!
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.
Thank you so much Sir.
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?
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 ""
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))
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
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 :))).
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 .
Hi Mike,
How can be formula further be extended to pick header of first filled cell??
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
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!!!
Sure, post a video response.
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!
Awesome! Thank you!!
awesome thanks
Good Job :) Thank You!
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?
Hi, Mike, what if I have text instead of numbers and would need to get the first cell which contains text?
Great, thanks a lot!
Mike,
Is it possible to submit questions for solutions or projects as a video?
Thanks.
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?
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
thanx alot
Why we will use 9.99E+307 in lookup value
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 ...)
Thanks
what if the data range have subtotal, how to exclude so that I can get the last number
Good video, but I Want Date 1, Date 2 etc. Instead of Numbers..Please reply..
How to fetch the last non 0 value?
What if the last 2 or 3 values are duplicates?
Is it possible to lookup an alpha numeric value
Sir, in row no 5, if i want second no i.e 12.5 than how can i do?
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?
I am sure there is. What is the logic of what the other is?
ExcelIsFun second third or other. But I got it already using small function . Thanks so much
@@hzaphry Cool : )
how to do the same for text entries?
Becouse it is fun :))))
Please help sir.