How to find last non-blank value - Excel lookup challenge

[@luuks4u]

Thanks Thanks Thanks A supor ton, I was literraly frustrated & nobody has helped me for this simple logic i was creating on google sheet, this has saved me.

[@haiderjawad2270]

At 05:29 instead of getting freedom i got 0, why? Didn't understand it,Please Guide

[@p_anjali_codes]

Thank you for the explanation, I was looking for exactly this.

[@1tusharjoshi]

I have been learning a lot from your videos and blogs. Following since 2011 (first time I started using excel) and still learning today.

[@chandoo_]

Thanks Tushar for continued love and support :)

[@wayneedmondson1065]

Hi Chandoo.. interesting challenge and clever solutions! Here is totally wacky one I put together, using the Reference version of INDEX: =INDEX((B4:B13,C4:C13,D4:D13,E4:E13),MATCH(K3,A4:A13,0),,COUNTA(XLOOKUP(K3,A4:A13,B4:E13))-COUNT(XLOOKUP(K3,A4:A13,B4:E13))) Admittedly more convoluted, but never the less fun to construct. Thanks for the good learning and inspiration on this one. Thumbs up!!

[@chandoo_]

Cool... It might be a bit too restrictive as you need to change the formula when there is 5th column.

[@wayneedmondson1065]

@@chandoo_ Hi Chandoo.. good point. Here's another using your MATCH("zzzz") trick: =INDEX(A4:I13,MATCH(K3,A4:A13),MATCH("zzzzz",INDEX(A4:I13,MATCH(K3,A4:A13),),1)) This one would be more flexible for expanding columns.. like your examples. Thanks for the "zzzzz" trick! Had not thought of that before.. excellent! Thumbs up!!

[@micheltw]

Thank you very instructive. In addition We can define the data as a table and the formula will automatically update if you add row or column(don't forget to add row 3 when updating formula for the first column)

[@cudacosmas-sai1868]

am using 2013, what if there is a formula that returns nothing in a "blank" cell ie invalid lookup and need to exclude it as last lookup

[@javadkhalilarjmandi3906]

Many thanks! It was very interesting and helpful

[@chandoo_]

You are welcome!

[@viveksharma4193]

same function how can we do that in power pivot dax function.. can pls suggest something

[@SuperChrisDub]

There is one drawback with your XLOOKUP version of finding the last non-blank cell - it does not work if the last cell contains a number, however, my version is very similar to yours and is kind of based on an old way using LOOKUP. So here it is =LET(fRow,XLOOKUP(K3,A4:A13,B4:I13),XLOOKUP(TRUE,fRow"",fRow,,0,-1)). this detects numeric and alphanumeric as well.

[@chandoo_]

That is awesome addition Chris. Donut for you 🍩

[@kunalsharma4895]

Hey buddy. Can you help me with something wherein we can use excel to send to email alerts to different people in the organisation. Like properly formatted excel tables or charts on the body of the mail along with the the file for review. Is it even possible in excel. It would be great help if you could suggest something.

[@OakleyTurvey]

Look at Ron de Bruin's website (Google it) for all sorts of ways to send emails from Excel, either in the body or as attachments, however you may benefit from having a single shared file on OneDrive / SharePoint and just sending links to it...

[@chandoo_]

As mentioned by Oakley, refer to Ron's website for some ready to use code. You can also see these page for more examples - chandoo.org/wp/send-mails-using-excel-vba-and-outlook/ - chandoo.org/wp/introduction-to-mail-merge/

[@kunalsharma4895]

Thanks to both of you will definitely learn from both the pages.

[@anukumarsingh1424]

Marvelous video chandoo, I have a question, can you help me please. Is there any trick or way to automatically fill the cell with particular formula. To be clear, i have a sheet and I asked user to input an integer value and as soon as user put an integer value, i have applied sequence formula using that value to create a sequence of that much row and After that on the adjacent column, i want a formula that should do something like add left cell value with 5, but as soon as the sequence over, i want nothing in next row not even formula.. since I am using sequence formula, hence I can't use table otherwise all the work will be very easy. Also adding the formula to whole column is not possible because of some constraints.

[@chandoo_]

Thanks Anu... Excel formulas cannot write themselves. You can either use IF to on / off the formula functionality or use some automation with VBA to auto-fill formulas based on the input data. Excel also offers # operator to dynamically stretch formulas based on the dynamic array ranges. If you are not familiar with it and not sure how it can help, watch this video - kzbin.info/www/bejne/oYOZlK2Bjpl5e9k

[@anukumarsingh1424]

@@chandoo_ thanks a lot.. yes i have watched your video, it's best.. but it only work on spill range i think.. but can #operator work on those dynamic ranges which are not in spill range..

[@anukumarsingh1424]

Also in future is it possible that you will make videos on automation tutorial i.e. VBA

[@MrDhunpagla]

Excellent Mr C............please help me out with this, what if I would like to get the column header of the last non blank cell value.

[@chandoo_]

You can follow the idea but use INDEX on the top row to get header info.

[@apparently75]

The XLOOKUP looks to be exactly what I need except my data has multiple values and blanks all in a single column that I'd like to return the last and largest non-blank value for that particular lookup value.

[@apparently75]

The list is technically unsorted but every instance counting down the column is the most updated info for that lookup criteria.

[@piyushkalra1098]

What is the logic behind using rept(z) or "zzzzzzz"?? and how does it work?

[@chandoo_]

I have explained that in the video. When you use "zzzzz" in lookups for approximate value, since there is no such value, Excel gives you the last cell with a value.

[@piks974]

What would be value for Number.

[@papachoudhary5482]

Thanks! Sir

[@shoaibrehman9988]

Thanks interesting

[@papachoudhary5482]

Requesting kindly make video on POWERBI. We are Waiting

[@chandoo_]

Hello Papa... Please see this video for getting started with Power BI. I have a playlist with more videos on Power BI too. kzbin.info/www/bejne/eXu6XoyYabWZkJo

[@papachoudhary5482]

@@chandoo_ Thanks! Sir, You are doing GREAT