Excel Magic Trick 986 One Lookup Value, Extract Multiple Items, Display Horizontally (A Closer Look)

One Lookup Value, Extract Multiple Items, Display Horizontally:
1. From given name list all groups that have selected that name horizontally.
2. Which means extract items with one condition.
3. Display items horizontally: use COLUMNS function for number incrementor.
4. "Data extraction" array formula
5. Problem: one lookup value, "Name", that shows up as multiple duplicates in a column.
6. Therefore you have many "matched" row numbers that the lookup function has to deal with.
7. Because we have an array of "matched" row numbers means we must use an array formula.
8. Lookup function will be: INDEX.
9. Function that can "select" correct "relative position" for INDEX function as we copy formula down: SMALL or AGGREGATE.
10. IF function or Boolean array operation will help us with our one condition "Name".
11. AGGREGATE is only in Excel 2010 or later. AGGREGATE can do array calcualtion in array argumnet for functions 14 to 19 without having to enter the formula with Ctrl + Shift + Enter.
12. IF with logical test is better to use than IFERROR because the IF will not run the entire array formula lookup function for cells that should return a null text string. IFERROR will run the entire array formula in every cell. This matters for formula calculation time.
Key Concepts:
1. One lookup value
2. Multiple "matches" in a column.
3. Multiple "matches" causes problems for standard lookup functions.
4. Multiple "matches" means multiple "Realative Positions"
5. We must then create an array of these "Realative Positions" in our formula.
6. This requirement that we create an array of relative positions makes this an array formula.
7. Then as we copy formula we must choose, one by one, the relative positions.

[excelisfun]

Actually, this is a good rehearsal for that chapter... It's all about telling a good story...

Thanks for the EXCELlent compliment!!

[Iheartfrogger]

Thanks so much! Clear and thorough explanations, unlike 90% of internet excel tutorials!

[wanghongsu]

I searched 17434 names against a 4000 name reservoir with 20 possible output using your formula, and it worked. Thank you, such a cool channel!

[ExcelStrategy]

I know that to prepare such kind of complex video require a very great effort, only a person with a real passion can do it.

[excelisfun]

Cool! I am glad that these videos help! It does help that it is a lot of fun to do (that is the passion part you are referring to)!!

[sayefuts]

Mike, I just don't have words to say thanks to you!! You are awesome!!

[MarkLee-cxes]

Thanks Mike, All Four Formulas Did The Job Perfectly !!!
I Appreciate Your Help,
Thanks A Million !

[ExcelStrategy]

I wonder how many people out there whatching this video fully understands what's going on inside these huge array formulas! Back to the video, I prefer to construct the comparative vector with the row-indirect-len but i think its a matter of preference :). I didn't know that the "IFERROR" is slow in some situation and using "If + a counter" is faster. Last thing microsoft shuld improve the "Aggregate" and enable it to handle array even with the other functions non only from 14 to 19.

[excelisfun]

Cool!

The ROW(INDIRECT("1:"&LEN(cell))) is great, though, I use it in many formulas that have to look at all the individual characters in a cell. Also, when you have a beg date and an end date and need to generate an array of serial numbers the ROW(INDIRECT()) is invaluable...

[WadeRaef]

Mike...your guru skills have elevated me to the next level here at work!

[Trogdorfan]

This was well above my level as a newbie, but I managed to get it to work through judicious copy and pasting your arguments. Thanks so much, these videos really help my work!

[excelisfun]

The main advantage that formulas have over PivotTables, Filter, Sorting is that they instantly update when source data updates. This is not always a requirement of the project at hand and so PivotTables etc., can be quite an advantage. However, in this example, I don't know how to make PivotTable do this. PivotTables make aggregate calculations with 1 or more criteria. If we did a Pivot for Phil for Number column, it would add or other aggregate calculation. This example is just data extraction.

[MarkLee-cxes]

Both Worked Fine, Thanks Again, U R Doing Good Job Here!!

[ttzJason]

Once again you have come through my friend and provided me with the answers when I was completely lost!  Many thanks as my co-workers are wondering how I make these spreadsheets work but that is my little secret.  Virtue follows those who are resourceful!  :)

[excelisfun]

I use the program Camtasia. As long as you act within the law, you don't have to ask Microsoft.

[excelisfun]

The best way to learn Excel at excelisfun at YouTube is to watch Excel Basics playlist and then Highline Excel Class playlist.

[pmsocho]

Mike, you are explaining that stuff so EXCELlent!
I guess it is one of the examples from the upcoming book :)

[ExcelStrategy]

Yup im too mouch enthusiast and wrote my comment too fast :), in a case like this one in place of LEN i use ROWS. Anyway maybe your solution calculates mouch faster. Another thig that I did not mentioned in my previus comments is the way you obtain the the relative position in the column beside the names ! I Find in it a great potential !

[rachelb]

Hi Mike - Great tutorial as always! Thanks. Quick question on how to build a year over year budget model to show effect of activities from prior year in current year. Would it work if I set it up vertically instead of horizontally?

[excelisfun]

Here are two more:
=LOOKUP(2, 1/(range>0), range)
=LOOKUP(1, --(range>0), range)