Excel index match retrieving multiple columns of data

I am trying to figure out what I could do to have excel automatically populate all four columns, not just one value, based upon the year, the company, and both of them combined. I made this spreadsheet as an example as it most closely relates to what I need it to do for a project of mine at work. I have a large master set with all of the unsorted data in it. I break it out into separate divisions in order to calculate returns based off each investment, each year, and then each investment in each respective year (and then I put everything in a matrix and it works great). The only problem I have run into now is every time I need to add a new investor and their return to the list, I have to add that one person four separate times into each division I have it broken out into. I am looking for a way to have excel automatically pull all four columns from the master list to the broken out division based on selective criteria. If you can help I greatly appreciate it.. please let me know how to type out the function exactly. See the attached picture to get a better idea of what I am talking about in case I didn't explain it well enough. Thanks so much, Ryan.

Undeclared cross-post at:

https://www.mrexcel.com/board/thread...lumns.1163210/

Welcome (back) to the forum.

Thanks for your question. However, we have just found out that you have posted the same question elsewhere.... and haven't told us. Feel free to cross-post on other sites...

But. Every forum has its rules. Please see Forum Rule #3 about cross-posting. So.

Do not keep it a secret. We all are willing to give you our time, freely. However, some of us get very annoyed if we waste our time developing a solution for you, when you already have a nice solution elsewhere. All we ask is that you show us some respect and tell us:

1) if you have cross-posted, and

2) the URL of the cross posts.

That way, those of us who don't want to waste their time can quickly check to see if you're already happy with another solution.

(Since you are new here, on THIS occasion, I have done it for you.)

As to the By Year sheet
Note that the table on the By Investor sheet needs to be resized to A4:H48
Try the following formula:

Formula:

Please Login or Register  to view this content.

Once the formula is entered in A11 drag the fill handle over to cell G11 and then, while A11:G11 are still selected drag the fill handle down to cell G25.
To populate the 2019 section copy the formula from cell A11 and paste into cell A28. Then change the $A$10 reference to $A$27 and then drag over and down as described above.
Note that the formatting of the Date column will need to be changed from General to Date.
Let us know if you have any questions.