Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column w Formula

Duplicate Lookup or Lookup 1 value, return many for a column Part 2. Formula to return all the numbers from a column that contains text and numbers. See an array formula that uses the functions: COUNT, IF, ROWS, INDEX, ISNUMBER, SMALL. Return Multiple Items From One Lookup Value (One Lookup value is a number).
Related Videos: Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table, Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row

Category:

Science & Technology

Tags:

• Excel
• Formula
• To
• Extract
• All
• Numbers
• INDEX
• MATCH
• functions
• Data
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Lookup
• One
• Item
• Return
• Many
• Items
• #2

[bl3nd3rs]

This has been a fantastic help. Your videos are great and very well explained. Thanks for allowing others to sit under your teaching without having to attend your college!

[ExcelIsFun]

@bl3nd3rs , You are welcome!

[dennisli828]

So can we use formula: =IF(ROWS($D2:D2)<=$C$2,SMALL(I­F(ISNUMBER($A$2:$A$10),$A$2:$A­$10),$D2:D2+1),"") for this purpose?? since SMALL(IF() ) will return the value directly, why bother using the index function?? please let me know

[ExcelIsFun]

No, I don't know how to get that formula to work.

[dennisli828]

@ExcelIsFun ok i figured it out, it works, but it will return the smallest value instead of the first one that matches the criteria

[ExcelIsFun]

Cool!

[gjamison72]

@confidentjohn I wish I knew what video you found that solved your problem. @ExcelIsFun I'm trying to return the top TEXT values in a column that contains some intermittent empty cells in that column. Is there a video for that? Normally I would use a filter and specify "blank" not to show, but I need to reference these cells as a Top 10 list from another sheet. If cells in a filter shift constantly then the references are shot. Thanks!

[ExcelIsFun]

You are welcome! That is why I post them - so that we can all have fun with Excel!!!

[confidentjohn]

@ExcelIsFun Hello Excelisfun. No worries any more I found a previous video from you that answered my question. I wanted to do a lookup on a value, and return multiple items but the items i wanted to return was text and not numbers.

But once again, thank you so much for this youtube channel, I can't tell you how many times you have helped me out.

John

[ExcelIsFun]

I do not understand your question, can you restate it?