Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula

Duplicate Lookup or Lookup 1 value, return many for a row Part 3. See a formula that looks up one value and returns multiple items. One To Many Relationship. See an array formula that uses the functions: COUNTIF, IF, COLUMNS, INDEX, SMALL and ROW.
Related Videos: Excel Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column, Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table

Category:

Science & Technology

Tags:

• Excel
• Return
• Multiple
• Items
• From
• One
• Lookup
• Value
• INDEX
• MATCH
• functions
• Data
• Extract
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Item
• Many
• #3

[AdamkiewiczJ]

Great tick, just one question, if I have long list of characters and it will not be possible to use the drop-down box, rather I will need a copy and paste form that will evaluate each new value...any hint...thanks basically copying across but also verticaly

[ExcelIsFun]

@AdamkiewiczJ , I don't have a trick for that. Try THE best Excel question site:

mrexcel [dot] com/forum

[knrd87]

this video was a perfect solution for my problem, thank you!

[ExcelIsFun]

You are welcome!

[cosmocashman]

Thanks... I've spent about 25 hours the past two days watching these videos for a project. My problem is im either overthinking it or getting lost. 1) I'm trying to filter through duplicates. Complete! 2) List the data(dates) from the duplicates in one row next to completed step 1). Any help or suggestions

[ExcelIsFun]

I don't know what you mean by "filter through duplicates". Do you want to get a unique list. If so try these two videos:

Excel Magic Trick 82: Advanced Filter Extract Unique Records

or

Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array...

[AdamkiewiczJ]

Great tick, just one question, if I have long list of characters and it will not be possible to use the drop-down box, rather I will need a copy and paste form that will evaluate each new value...any hint...thanks

[ExcelIsFun]

You are welcome!

[Mickoes]

That is amazing, you just solved in one formula so many problems I've been asking myself for the past two years. Thanks again!

I used the concatenate function (text+date) + countif to automatically return multiples values of a specific week.

Thanks again!!!