5 Advanced Excel Formulas You Probably Didn't Know!

Learn 5 advanced Excel formulas to become an advanced Excel user.

In this tutorial we go over 5 advanced excel formulas. The first function is the TEXTSPLIT, where we use several delimiters to split columns of data. Second, we go over the SUM and OFFSET combination using a nested formula to make a dynamic sum range. Third, we learn how to use the XLOOKUP with the wildcard feature to detect inconsistent data. Fourth, we use the INDEX MATCH with the ampersand (&) to use multiple criteria to lookup values. Finally, we learn the SUM FILTER combination which is most useful to sum ranges with duplicate filters.

LEARN:

SOCIALS:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ Level 1 ADVANCED TEXTSPLIT
2:28​ - Level 2 SUM OFFSET
4:19​ - Level 3 XLOOKUP WILDCARD
6:53 - Level 4 INDEX MATCH AMPERSAND
9:05​ - Level 5: SUM OFFSET

Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.

[alexrosen]

Extremely useful tutorial because of the different hacks in for example the filter function when in combination with other formulas can do magic. A lot of tutorials shows the filter function in it's basics which is good but can be quite repetitive and tedious, especially when you are a learner who wants to take the next steps in the learning curve.

[nurulsarifah]

For number 4, we can use double xlookup ;) =XLOOKUP(C3, B10:B29, XLOOKUP(C4&C5, C8:K8&C9:K9, C10:K29)). I am a beginner here. I have watched +10 of your videos and found that double xlookup = index match. Thank you so much. Your videos are very helpful and you are truly amazing! Please keep it up!

[TheFlying-Cyclist]

Hey Kenji, great content you are posting. In fact, I ended up subscribing to one of your courses after a video I watched a while back. I'm curious, though, how would one start freelancing with the skills learned from your course(s)? I am taking Excel for Business and Finance and Power BI for Business and Finance. Looking forward to hearing from you and thank you in advance.

You are a born video instructor. Your voice is engaging, your speed with the mouse is not too fast and not too slow, and your progression made sure I did not get lost. I am absolutely coming back to watch this again when I need another refresher. Subscribed!

[bobby]

Great job building the complex formulas in steps with great detail.

[manosvenianakis]

Excellent video and excellent presentation! Thanks!

[Servants_Heart]

Very nice. I had to learn excel before there were videos, classes, and manuals. I officially have subscribed.

[sevetp]

Great video :-) For #4, I would suggest this formula : whose syntax is sumproduct((criteria_range1=value1)*(criteria_range2=value2)*[...]*(value_range=range)), where "*" stands for AND and "+" stands for OR. Note that you need to end your formula bay value_range.

[SaiSanjeevie]

Really Super man. Clear and Beautiful explanation of new subject in Excel...

[chrisgray]

I have a table similar to your example #4, only the first two columns are row labels and the top single row is column headers (quarters 1 to 4). I couldn’t get the formula to work, so I replaced the dataset array with INDEX(dataset, 0, MATCH(column_header, column_header_row, 0)). This made the array a single column. I used the & with the first two columns, and it worked. Thanks for the neat tricks. It was a good presentation.

[owrsrlt]

Thank you so much Kenji; your explainations are extremely clear, you give us the examples files. I don't how to say that once and of all: THANK YOU !

[mohamedsoffar]

Really cool what your sharing, this is amazing, Thanks Dear

[Spreadsheets_Plus]

Nice one kanji and thank you so much for this. And the XLOOKUP function as well can perform the same advance index match which you did. Great work

[ruminsb]

Nice Sir. Your Explain Very Good. Thank Sir...

[mos]

Kenji can you show the substitute function for textsplit as i use office 2021 its doesn't have that function it would really helpful for me

[standoffsniper]

You don't need to put 2 filters, you can write it like this =Filter(array, (name=billy)*(year=2021)) of course the name and the year can be referenced

[Besavant]

Sir, Can you please make a video regarding a excel workbook in which there will be two sheets in one of the sheet there will be database about our invoices like invoice number, invoice date and invoice amount. the another sheet will ask us to enter a value which we will enter manually but after we input the amount the second sheet will show us all the possible combinations of invoices which total upto that amount which we entered. i have been facing this problem during my work and for this i have to manually search and check which invoices total upto certain amount and then allocate payment to them. but i hope you can help me with my problem and make video for teaching how to solve for this issue.

[ahmedkamon]

Very impressive.
I have a column contain text or numbers, How can I search a certain value in that column in a another table contains columns with text and numbers

[mostafaiffakh]

Thank you Kenji, very useful
Please, how about more then three criteria.

[franzzzis]

Your explanations and examples are excellent, which are also suitable for beginners with Excel.
There are countless tutorials on Excel, from simple to highly complex, but only a few are really good, your videos are definitely among the top 20 for me.
compliment