Retrieve The Correct Column Header (Leila Gharani Kevin Lehrbass Excel Challenge)

This came from Leila Gharani
Then Kevin Lehrbass took it on:

The challenge:
You have data in a matrix. Locate a specific piece of data and retrieve the name of the column it's in.

This is tougher than you might think.

This video is a glimpse of the solutions from
Leila (COLUMN, SUMPRODUCT and INDEX) and
Kevin (Array Formulas, Helper Columns, MIN, FIND).
Then I show 2 solutions:
- Formulas (COUNTIFS, INDEX)
- Get & Transform (Unpivot)

Take a look!

My book: Guerrilla Data Analysis 2nd Edition

[LeilaGharani]

Love it! PQ is it! Thanks for the great production and sharing your version of the challenge. It's so nice to see the different approaches. Love the thumbnail and the visual effects :)

[entertainmentgalaxy]

U turned array formulas into simple solution in just few clicks.. Thanks for sharing

[rockguitarist]

Fantastic Oz! And thanks to Leila and Kevin for their solutions as well. So fun!

[michaeljones]

Thank you,
I really love seeing these real world examples. It helps me to see my data in all sorts of ways. It helps me to keep growing with Power Query/Excel

[MySpreadsheetLab]

Cool approach Oz! And like always excellent production and props 😀

[barttitulaerexcelbart]

This is a typical example how a rather complex problem is solved by some experienced Excel users. We first try to use simple Excel formulas, if that does not work the more sophisticated formulas come out of the toolbox. And in the end there is also PQ. Well maybe we should start looking first to PQ. You help us with that Oz, thank you!

[uchennaanunike]

Good Sir, I saw @LeilaGharani video which brought me to you, what I want using Leila matrix, is an spill formular that will return the division of the game instead of the game. So, the result of the spill is where sloops is, productivity will be returned, also where is productivity will also be returned, and where fightrr is, games will be returned, twister will also return games. All these in one formular

[enricodecrescenzo]

no more pussyfooting... Brilliant! Oz, you always know best!
Great outfit by the

[Jivolt]

"There's no way this channel could get any better!" - us

"Oh, really?" Oz before this video

The outfit + "pussyfooting" = damn near internet exploding.

[ankurshukla]

Another approach with MMULT function without any array confirmation=INDEX(B$4:D$4, , MAX(MMULT(N(ISNUMBER(SEARCH(H5, $B$5:$D$45))), {1;2;3})))

[XLarium]

Help, the Mandarin has taken over the channel!

[joannpaules]

All dressed up and nowhere to go? Go to Youtube and watch an Oz video!