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?
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 :) 😀
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.
@@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.
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 👍
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.
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. :-)
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 👍👍
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!
Hi Vijay
Thanks for the feedback. But which is your preferred function?
@@Officeinstructor Well, that's really a tricky question, but I like INDEX-MATCH combination followed by XLOOKUP!
perfect .) one little input, on 16:30, geen box, there is missing a comma to skip the argument
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?
@@Officeinstructor XLOOKUP for me. That trick with the wildcard and VALUETOTEXT was really great!
Excellent tutorial Nabil. I prefer the XLOOKUP method because of its simplicity.
Very clear and useful explanation
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 :) 😀
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.
@@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.
@@nadermounir8228
earning how to drive a manual car first then driving an automatic
That is the BEST analogy I have ever heard
Wow, xlookup is the best! Great! Thanks Nabil.
Great Choice Luciano
Xlookup 👍
Good choice
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 👍
Thanks Martin for watching and sharing your feedback.
Perfect Sr
Most welcome Usman. Did you check the blog article?
@@Officeinstructor I check it Sr
Match & lookup
Great Functions
XL. No contest. MAX Easy. MAX Robust
Good Choice Richard
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.
Hello. Thank you for your knowledge and quality of explanations. What happens if you write "?*" instead of "*?*"
Index and match
U r Excel wizard.
Thanks for the compliment
Dear sir
Would you please make video base on conditional formatting and custom number formating .
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. :-)
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
@@Officeinstructor Aaaarg… can’t wait! :-)