Using Lookup or match to display a list of results from a range of cells

[jshaw82]

Hi All, I'm looking for some ideas on how I can get a formula to display the names of individuals associated with a group, I've tried a vlookup without success, i've attached an example. Maybe a "Match" function, but i'm not great with them.

There is a list of names (in the example its 8, but could be upto 12). I have two tables below the list with group 1 and 2 next to the names it identifies the group a certain person is in 1 or 2. I want the first table to list all the names in group 1 and the second in group 2. There is the potential for a 3rd group but I though once i'd got an idea on how to do this i can work on the rest.

Thanks in advance

John

lookup.xls

[Pepe Le Mokko]

Why not use a filter ?

[jshaw82]

Hi, It's because i'm going to be doing something else with the columns to the right once they are grouped

[xwarlock10x]

here is a great video on just this subject..

https://www.youtube.com/watch?v=132ZdpxBm1U

[Lemice]

Hello,

You can use a simple Array formula for that.

Steps by steps how to do it:
1. Un-merge all of those cells
2. Make sure the formula is being wrapped inside a { }. If not, click on the formula bar, hold Ctrl-Shift and hit Enter.
3. Write down a note somewhere, "Don't merge a cell if you want to put a formula on it. Don't merge cell in general. Just don't, they are evil!"

And here is your sample file with the formula.

[jshaw82]

Hi Lemice. Thanks for the tip about merged cells. I dont know what i was thinking! I have used array formulas before but i dont really understand them so i tend to go for non-array. But you solution looks good so i'll be using it!

[jshaw82]

xwarlock10x, thanks for the youtube link. There's some other interesting stuff there that i may need for future sheets!

[jshaw82]

Hi Lemice,

I've been trying to incorporate this array in my book. As the book layout is slightly different to my sample file (i thought I'd be able to manipulate the formula). I have formatted the sample book to how the layout is in the book i'm working on. I've also expanded the group as there may be more names in the list for the group. Can you advise me what is wrong?

Book attached:

lookup2.xls

[Lemice]

This is your formula in B25

Please Login or Register  to view this content.

And this is how it should have been

Please Login or Register  to view this content.

At your first sample, because the data starts from row number 2, so instead of putting ROW($A$2)+1 at the end, I just put in a -1 (I'm sorry, I was lazy ...) This was necessary to exclude the headers, making the counting starts from first row with data as 1, and as it moves down 1 row, it becomes 2.

Here is your formula in B31

Please Login or Register  to view this content.

And here is how it should have been

Please Login or Register  to view this content.

Similarly, you will have to minus the row of the header and plus one, plus keeping the end of the formula as row(B1). This also starts with the purposes of starting the SMALL formula with k = 1.

For more information, please refer to the post #5 and #11 on this thread
http://www.excelforum.com/excel-form...-in-order.html
(Yes, I'm sorry I was being lazy).

Here is your sample with the formula.

And don't hesitate if you have any other question.

[jshaw82]

Thanks for your support, I really don't know how these arrays work I've only used them in one other book before and that was with some help. It's not lazy. I appreciate the help! I will be looking at it in the morning.