Lookup Last Non Empty Cell Value in Column or Row - Six Methods

You might have an Excel situation where you need to find the last non-empty cell value whether in a Column or in a Row. Things can get more difficult if you lookup the last value based upon a condition.
In this tutorial I show you 6 methods to lookup the last non-empty cell value From Simple to Mind Blowing methods. Whether you have Dynamic Array functions or Not… This tutorial has you covered.
Let me know in a comment, which method you prefer?
You can Download the Exercise File and Read the step-by-Step instructions by clicking on the link:
Method 1 ► LOOKUP Function
Method 2 ► OFFSET function
Method 3 ► INDEX Function (with a trick)
Method 4 ► XLOOKUP Function
Method 5 ► WildCards with XLOOKUP
Method 6 ▼▼
The next method is the most robust and dynamic one, where I have thousands of sales transactions, and each client placed many orders and I just want to extract the LAST transaction for any selected client.
I used a nice trick in Power Query to create this solution in a separate video.
So, Subscribe to my channel and ring the bell, to be notified when I post this amazing solution.
Without any further delay, let’s dive in

[UpExcel]

Genius idea to put NUMBERTOTEXT inside an XLOOKUP like that. Great stuff all-round Nabil 👍John

[gandhisunil]

Xlookup is my favorite, I learnt the real meaning and application of value to text function...the way you present is superb and commendable 👍👍

[vijayarjunwadkar]

Wow! Thank you Nabil! You opened a treasure trove of ideas for me! So many tricks in one video and I can't wait for the Power Query solution!

[IvanCortinas_ES]

Excellent tutorial Nabil. I prefer the XLOOKUP method because of its simplicity.

[Luciano_mp]

Wow, xlookup is the best! Great! Thanks Nabil.

[nadermounir]

Absolutely brilliant Nabil 👏 👌. I personally prefer the match function because it is the old school method that everyone has access to. I do have 365 beta version though. What I really liked the most in this video is the Valuetotext function as this was new to me. Looking for forward to your upcoming power Query solution :) 😀

[martyc]

Xlookup for me- I always get caught with text and numbers and sometimes wrap the lookup in value function and sometimes I wrap it in the text function, but I really like the trick in wrapping the array in valuestotext- a function I have not heard of!!- I must try it Thanks Nabil 👍

[dominiquehandelsman]

Hello. Thank you for your knowledge and quality of explanations. What happens if you write "?*" instead of "*?*"

[ursula]

perfect .) one little input, on 16:30, geen box, there is missing a comma to skip the argument

[shineday]

Mr. Nabil, I really appreciate if you can tell me a formula I have been looking for so long. Assume the same table used in method 4 where there are empty rows. Suppose that column A cells of empty rows are filled with item names and their corresponding cost are all zero or blank and I want to return the last non blank cost for a specific item name. In this case the blank cells are present in the reference array rather than the look up array.

[RafiqulIslam-dvcu]

Dear sir
Would you please make video base on conditional formatting and custom number formating .

[sarthakchandekar]

i followed all these steps but when you are using sample that you have imported the program just bugs up.

[GeertDelmulle]

XLOOKUP for me. That ‘trick’ with the wildcard and VALUETOTEXT was really great!
In PQ I would add an index, get the last value of that column (there’s a function for that, then again PQ has a function for (almost) everything :-) and use the that result to extract the last record. I’m eager to see your solution. :-)