Trying to find multiple ID numbers which match a particular name from a list.

[thevolv]

I have 2 spreadsheets with many rows (in the tens of thousands) and columns (about 20 or so) of data.

I am trying to take the ID# from one spreadsheet and append it to the other, based on a name match. Typically I would use a vlookup, but, in this instance, most companies appear more than once and they often have many different ID#s associated with them. I am looking to return each unique ID# that is associated with each company, and display them horizontally next to that company's name.

Since this is including a lot of private data, I have included a small, generic example of what I am looking to search off of and output. Sample Spreadsheet.xlsx

I have seen examples of how to return multiple values using index functions, but, they all seem to only be able to handle one name at a time, and it displays the values vertically from that. Since I am trying to do this for thousands of rows of data, this won't work for me. I need to be able to have a function/formula of some sort that I can apply to each and every of the thousands of rows of data simultaneously. And, on top of that, I'd like to be able to display the 2nd, 3rd, etc ID#s for each company in additional columns of data.

Is this at all possible, or am I trying to bite off more than I can chew?

[AlKey]

In E2

Copy and paste this array formula. Drag it across and down.


=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$10=$D2,ROW($A$2:$A$10)),COLUMNS($D$1:D1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[thevolv]

Awesome! That worked like a charm! Took a little while to process (obviously), but, it did exactly what I needed.

Thanks a bunch! That saved me hours of work and a serious headache!

[AlKey]

You're welcome.