Determine Cause of

[@excelisfun]

Updated Video 4 PM Pacific Time Jan 3, 2025 : )

[@GaryMillerUK]

Not sure what I enjoyed most here, the interesting content if the video or the textbook way you annotate and timestamp chapters in the description text. Bravo!👏

[@flexyourdata]

Great video and well-explained. I wish I had a fraction of your enthusiasm and knowledge!

[@excelisfun]

You can definitely get all the enthusiasm and knowledge of Excel that you want: just have fun with Excel : ) : )

[@kiwikiow]

Good tricks to clean data. Thank you Mike 🙂

[@excelisfun]

You are welcome, teammate!

[@LambdaBam]

The reduce function is over powered! I remember when we learned about it 3 years ago, and we thought we could replace it with the sum function 😂! Little did we know what a treat MS had for us!

[@excelisfun]

I 100% remember that also. Remember someone told me the first day I saw it: don’t worry it looks like a silly replacement for some, but we will figure out how to leverage it later : )

[@johnborg5419]

Very intetesting!!! Thanks Mike.

[@excelisfun]

Yes, Formula Guy John!!! We are lucky to be on a great Team : )

[@chrism9037]

Thanks Mike! These are great tips

[@excelisfun]

You are welcome, Chris M!!!!

[@grandepatron]

This is great I had this issue just the week!

[@excelisfun]

Cool! Glad it helps contemporaneously.

[@shubhampawar8506]

Great Trick ❤

[@excelisfun]

Glad you like it!!!!

[@RAVISHARMA-kk8lu]

Gjb guru

[@bohdanduda652]

fantastic!

[@excelisfun]

Glad you like it! Go Team!!!!

[@rtrbs8383]

Happy New Year...Sir. Thank you for this Video. I think we can directly give the CHAR to array value in Reduce. =XLOOKUP(REDUCE(C16,CHAR(E13#),LAMBDA(t,d,SUBSTITUTE(t,d,""))),C7:C9,D7:D9)

[@excelisfun]

O, I like it!!! No F9 to get hard coded array. Thanks : ) : ) : )

[@m7mmad3]

Thanks Mike! I have question please. I have a list of all employees of acompany, and I want to identify the employees of a specific department by entering the head of that department, such as the CHRO. The goal is to retrieve a hierarchical structure where the CHRO is the direct manager of a group of managers, and then display the employees reporting to those managers in a hierarchical format. This way, I can generate a list of all HR employees by simply entering the CHRO's name. Is it possible to achieve this using a formula?

[@excelisfun]

Anything is possible in Excel. You can use FILTER to retrieve the names based on CHRO. I do not understand what the hierarchical structure is, but I am sure Excel can do it.

[@jete7810]

Enjoy your tutorial videos.

[@excelisfun]

Thanks for your insight : ) For this data set, you can just use the formula: =LOOKUP(C12:C16,C7:D9). But as is exposed in the comments below, since LOOKUP does approximate match lookup, it will always find the next smaller. If the white space characters are in other places, like the beginning of the text, or interspersed within the text, which is not uncommon, the formula will yield an #N/A!

[@jete7810]

@@excelisfun Thank You for the reply. I am using the trial version to work through your tutorials on the new functions in Excel, as I was not sure that my six-year-old computer would be capable of running 365. REGEXREPLACE does not seem to be supported by this version, so when I upgrade to the full subscription I will test XLOOKUP(REGEXREPLACE... again. I hope that you have a blessed day.

[@rob-fb5xs]

I often get errors when using vlookup and xlookup that I fix by using the text to columns function on the data. Sometimes i change the values I’m looking up and sometimes the values I’m looking up to. I get these errors so often I just go straight to the text to columns function without thinking and it usually makes the vlookup or xlookup work. Is there a better way to fix this common error.

[@excelisfun]

You can make a reusable function using the LAMBDA function: 1) In Defined Names (Ctrl + F3), create the new name: CleanLookup 2) Write a description in Comment area like: Lookup function to do exact match and clean all white space characters. 3) In the Refers To text box, paste the formula: =XLOOKUP(REGEXREPLACE(lookup_value, "\s+", ""), match_array, return_array) =LAMBDA( lookup_value, match_array, return_array, XLOOKUP(REGEXREPLACE(lookup_value,"\s+",""),match_array,return_array))

[@rob-fb5xs]

@@excelisfunThanks for replying so quickly. I’ll try this out and let you know how I get on.

[@matthewstone7367]

I would have used left(text, 9) in this situation

[@excelisfun]

Yeah, me too. If we knew for sure, the character was at the end. I put the characters at the end just to make the first part easy to illustrate. It was really the last example where we don’t know how many characters there are or where they are That is useful.

[@matthewstone7367]

@@excelisfunI think this could be the only approach that would work when importing and compiling names (people, products, places, etc.) from different locations. I enjoyed the derp dive into the how to handle such characters. Thanks for making great videos!

[@excelisfun]

@@matthewstone7367 You are welcome for the deep dive. The deep end of the pool always has the whys that then help use solve problems creatively : )

[@hashi856]

Couldn't you just have used CLEAN? Although, that does only work for ASCII. Still worth a mention, in any case.

[@LambdaBam]

Clean will not fix CHAR 160. It really is a headache 🥶

[@excelisfun]

CLEAN function is a great function, but I can’t get it to remove character 160. Which is a character that shows up a lot when we copy and paste.