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

[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!

[confidentjohn]

Hello, probably the best resourse I have used for excel help. so thank you ever so much.

I was wondering if there is a way to convert this to pick up a specific text value.

Hope you can help

Cheers

John

[ExcelIsFun]

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

[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]

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

[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!

[csemanate]

Dear Mike, I was wondering how would you perform this technique dealing with arrays, not with ranges. Let me explain: in this video you have data in the range A2:A10, and the result is presented in range D2:D5. I would like to know how with an array or 9 rows containing numbers and text like in the example, you would obtain a 4 row array containing numbers only. Thanks in advance for your help.

[ExcelIsFun]

I do not understand your question. What array? What 9 rows or 4 rows? Can you give me more detail?

In this video the range (A2:A10) contains data in different rows and the formula returns data in different rows (D2:D5) - even though all the values are in columns.

[csemanate]

@ExcelIsFun

Sorry, here is the detail: Applying IF with ISNUMBER I get this array: {"";23;"";"";"";54;"";45;44;32­;"";2}, and I would like to take this array and eliminate the "" elements. The desired result would be the following array {23;54;45;44;32;2}. Hope it is clear enough. Thanks

[ExcelIsFun]

See e-mail I sent (YouTube would not allow me to post formula in the comment section).

[chinchonchinchon]

Dear Mike, I just wanted to tell you that this one I finished by myself. I watched 358 several times until I understood what was going on, so I figured this one would be similar and it did work. I've tested it adding numbers to this one and it did the trick.

This is to let you know that you are a great instructor and your personal attention to each one of us is unique. I wish all teachers in the world were like you.

Thanks again. :-)

[ExcelIsFun]

Thank you very much for the EXCELlent compliment!

You are welcome for the videos!

And what is so cool about me helping you to learn, is that once you learn it, you can help others to learn!!!

Play it forward, as they say!