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

Here is a tricky Excel lookup problem. You want to find the last non-blank value for a lookup value. Each value you want to lookup has multiple items in adjacent columns. You need to get the very last item as answer.

Watch the video to learn 3 possible solutions to this problem.

You can download sample workbook there.

#lookupLastNonBlankValue #vlookup #xlookup

[luuksu]

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.

[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)

[tusharjoshi]

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

[p_anjali_codes]

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

[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.

[wayneedmondson]

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!!

[javadkhalilarjmandi]

Many thanks! It was very interesting and helpful

[cudacosmas-sai]

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

[apparently]

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.

[haiderjawad]

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

[anukumarsingh]

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.

[MrDhunpagla]

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

[viveksharma]

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

[papachoudhary]

Requesting kindly make video on POWERBI.
We are Waiting

[kunalsharma]

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.

[piyushkalra]

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