XLOOKUP & Dynamic Arrays Celebration and Demos with MVPs Wyn Hopkins and Oz du Soleil

XLOOKUP and Dynamic Arrays are finally available for anyone on Microsoft365 (formerly known as Office 365). In this video, Wyn Hopkins and I give you demonstrations of XLOOKUP and Dynamic Arrays working together to make simple but robust solutions.

0:00 Opening
1:34 Wyn's Demo
6:15 Oz's Demo
10:40 Wrap-up

You'll see it all!
XLOOKUP
FILTER
2-way lookups
SORT
TRANSPOSE
Tables

It's one big party, y'all!

#XLOOKUP
#Dynamic_Arrays
#DynamicArrays

For a list of my Excel courses at Lynda/LinkedIn:


There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.


My book: Guerrilla Data Analysis 2nd Edition

[Officeinstructor]

Yess, Life is so much better with XLookup, DA, OZ and Wyn... Thank you for the celebration

[IvanCortinas_ES]

What a combination!!! Excel is something magical with videos like this. Thank Oz du Soleil and Wyn Hopkins!!!

[jelle]

Very hot stuff 🔥🔥!!
Thanks for sharing van making this excellent video.
All the best for you guys!

Regards from the Netherlands.

[nonoobott]

Awesome sturvs. Your videos are so exciting and insightful too. Learned new things about XLOOKUP and the dynamic arrays. Thanks for sharing

[eldersnackwrap]

You've got me pumped to try out these new xlookup and Dynamic Array functions. I'm going to update first thing in the morning!

[DougHExcel]

Double team magic describing these functions generally available on MSFT365! woot woot!

[StrangePieceOfCake]

the excel scene got crossovers now? loool this is awesome

[AccessAnalytic]

Awesome stuff Oz, Double XLOOKUP!!! 🔥🔥 plus your video editing skills are a thing to behold

[wayneedmondson]

Hey Guys.. cool tag team concept.. love it!! Been learning some lessons over at ExcelIsFun.. how to use dynamic array functions to include dynamic row totals, column totals and grand totals as part of a cross tab report created by using spilled arrays. Against your data, here is what I came up with:
Cell G5: =IF(SEQUENCE(COUNTA(UNIQUE(Table2[Exterior]))+1)>COUNTA(UNIQUE(Table2[Exterior])), "TOTAL", SORT(UNIQUE(Table2[Exterior]))) - adds "TOTAL" to the end of the sorted, unique list
Cell H4: =IF(SEQUENCE(, COUNTA(TRANSPOSE(UNIQUE(Table2[Type])))+1)>COUNTA(TRANSPOSE(UNIQUE(Table2[Type]))), "TOTAL", TRANSPOSE(SORT(UNIQUE(Table2[Type])))) - adds "TOTAL" to the end of the sorted, unique list
Cell H5: =IF(G5#&H4#="TOTALTOTAL", COUNTA(Table2[Exterior]), IF(SEQUENCE(COUNTA(G5#))>COUNTA(G5#)-1, COUNTIFS(Table2[Type], H4#), IF(SEQUENCE(, COUNTA(H4#))>COUNTA(H4#)-1, COUNTIFS(Table2[Exterior], G5#), COUNTIFS(Table2[Type], H4#, Table2[Exterior], G5#)))) - the magic formula.. all the appropriate results.. including totals
These 3 formulas will create your dynamic cross tab report and include a dynamic TOTAL row, TOTAL column and GRAND TOTAL that will react dynamically to new data or other changes in your data. Conditional formatting can be added to dynamically emphasize (bold, backfill color, italics, etc.) the totals. I'd love to say that I came up with this, but credit to Mike Girvin and friends for the technique. I just modified it to work on your example. The fun is really endless with new dynamic array functions. Thanks to all of you MVPs for showing what can be done with them :)) Thumbs up!!

[ivanmamchych]

Thanks for the good news, Oz and Wyn!
I've been waiting for new functions almost a year having company O365 Build 1908. Hopefully it will knock at our door pretty soon with the next update
That'll be a new era in a regular using of Excel
New discoveries are to come…

[GeertDelmulle]

Nice little report you built there, Oz. Now, can you add a Totals Row and Column to your report?
Dynamically, of course. ;-)

[spilledgraphics]

06:18 Take your clothes off @Oz ???? 🤣🤣🤣

[shoaibrehman]

Hi Oz another cool video, As you are Power Query Guru I have one case to find unique "Donuts" sold in each month in year data.
Criteria Unique donuts, less than $5.
In future videos of PQ if you can demonstrate.
Thanks
Take Care

[wesszep]

😭 Don't have😭, but it's great!😁