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:
officeinstructor.com/lookup-t...
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
Пікірлер: 38
Genius idea to put NUMBERTOTEXT inside an XLOOKUP like that. Great stuff all-round Nabil 👍John
Xlookup is my favorite, I learnt the real meaning and application of value to text function...the way you present is superb and commendable 👍👍
@Officeinstructor
Жыл бұрын
XLOOKUP is amazing. Thank you Sunil for the very nice and encouraging comment
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!
@Officeinstructor
Жыл бұрын
Hi Vijay Thanks for the feedback. But which is your preferred function?
@vijayarjunwadkar
Жыл бұрын
@@Officeinstructor Well, that's really a tricky question, but I like INDEX-MATCH combination followed by XLOOKUP!
Excellent tutorial Nabil. I prefer the XLOOKUP method because of its simplicity.
Wow, xlookup is the best! Great! Thanks Nabil.
@Officeinstructor
Жыл бұрын
Great Choice Luciano
Very clear and useful explanation
@Officeinstructor
Жыл бұрын
Glad you liked it
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 :) 😀
@Officeinstructor
Жыл бұрын
Sometimes I think I am lucky that I worked with classic functions for 2 decades (and I love them) they taught me how to build the logic of a complex function. may be I couldn't do that if I just started using Excel in the Dynamic Arrays generation. Thanks Nader for being always kind and motivating.
@nadermounir8228
Жыл бұрын
@@Officeinstructor that is why I like to learn all the old school array formulas using control shift enter. It is like learning how to drive a manual car first then driving an automatic will be easier.
@Officeinstructor
Жыл бұрын
@@nadermounir8228 earning how to drive a manual car first then driving an automatic That is the BEST analogy I have ever heard
Perfect Sr
@Officeinstructor
Жыл бұрын
Most welcome Usman. Did you check the blog article?
@usmaniqbal1836
Жыл бұрын
@@Officeinstructor I check it Sr
perfect .) one little input, on 16:30, geen box, there is missing a comma to skip the argument
@Officeinstructor
Жыл бұрын
Hi Ursula, Thanks for watching. Despite the INCREDIBLE effort I put in video editing, I missed this one :( but luckily, I did it right in Excel and I explained it correctly:) You didn't mention your preferred function?
@ursula9875
Жыл бұрын
@@Officeinstructor XLOOKUP for me. That trick with the wildcard and VALUETOTEXT was really great!
Xlookup 👍
@Officeinstructor
Жыл бұрын
Good choice
Hello. Thank you for your knowledge and quality of explanations. What happens if you write "?*" instead of "*?*"
XL. No contest. MAX Easy. MAX Robust
@Officeinstructor
Жыл бұрын
Good Choice Richard
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 👍
@Officeinstructor
Жыл бұрын
Thanks Martin for watching and sharing your feedback.
Match & lookup
@Officeinstructor
Жыл бұрын
Great Functions
Dear sir Would you please make video base on conditional formatting and custom number formating .
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.
Index and match U r Excel wizard.
@Officeinstructor
5 ай бұрын
Thanks for the compliment
i followed all these steps but when you are using sample that you have imported the program just bugs up.
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. :-)
@Officeinstructor
Жыл бұрын
I'm glad you liked the tutorial, my friend Geert In PQ, I used to do it the way you mentioned, UNTIL I found a different method... Stay Tuned
@GeertDelmulle
Жыл бұрын
@@Officeinstructor Aaaarg… can’t wait! :-)