List of data based on name

[sinspawn56]

Hello,

I have some raw data that contains a name and a reference number among other things. On another sheet I would like to pull the list of all the reference numbers for a name from the raw data. I tried using an index mach but I cannot figure out how to pull the next consecutive reference number for that name.

I have attached an example.

[JieJenn]

How about advanced filter?

[sinspawn56]

That would work, but I would rather have a formula do it, because I am going to be adding more and more raw data too it in a regular basis and this is just one more step. I appreciate the suggestion though.

[mmmarks]

Is this what you are looking ?

[sinspawn56]

That works. Thank you.

[liaos]

HI, thank you both for sharing the example file.
It has helped me a great deal.

However, in the example, this formula goes from A$2:$A$21
=IFERROR(INDEX(data!B:B,SMALL(IF(list!$A$1=data!$A$2:$A$21,ROW(data!$A$2:$A$21)),ROWS(data!$A$2:A2))),"")

I have a huge table with over 300 rows.
But when I tried to apply this formula, it will only pull data up until Row 21.
I have tried to modify the number from 21 to 321, but then it will stop working.

Can you tell me what was wrong and how I can increase the range?

Thank you so much.

[sinspawn56]

Once you change your row value, you have to enter the formula as an array. You do this by holding down CTRL+SHIFT then press enter.

[liaos]

example.xlsxThank you for your prompt response.
However, I did Ctrl+Shift+Enter to type my formula with the new row value, but it still didn't work.

This is the original one that works (up to row 21)
=IFERROR(INDEX(data!B:B,SMALL(IF(list!$A$1=data!$A$2:$A$21,ROW(data!$A$2:$A$21)),ROWS(data!$A$2:A2))),"")

This is the one I've modified
=IFERROR(INDEX(data!B:B,SMALL(IF(list!$A$1=data!$A$2:$A$321,ROW(data!$A$2:$A$321)),ROWS(data!$A$2:A2))),"")

Did I miss something? The modified formula simply didn't work. (And I tried several variations as well).

Here is the link to the example file - http://dl.dropbox.com/u/4658634/example.xlsx
Again, thanks you so much for your help.

[sinspawn56]

It seems as though your formula would work. Do you have an example that you could attach?

[liaos]

Yes, here is the example - http://dl.dropbox.com/u/4658634/example.xlsx

[liaos]

If I type "John" or "Ben" or "Mandy", I got different result.
Which is great.

However, it never display anything pass Row 21. And I would like it to go through the entire list and pull the result.
Thanks.

[sinspawn56]

It seems to be working for me. I have attached your example file.

[liaos]

That is strange. I opened my own link and modified it and it still didn't work.
But when I opened yours, then it does.
I even modified your file from Row 200 to Row 300 and it stays working!!!

YAYYYY. ^_^
Thank you so much for your help.

[sinspawn56]

No problem!