Excel Dynamic Index Match Issue

[wizchiz]

Hello! I am curious if anyone would have the solution to this or could work with me to come up with a solution. I am trying to produce a table and some values from a table of historical data. Here is a quick overview of what I have so far and then I will get into what it is that I would like some input on. And I wanted to stay away from using macros or VBA to make this process easy for the simplest user. And I wanted to make this a dynamic chart that can easily be generated.

So, I have a table of historical data that goes from 2007 to present that is updated monthly. It consists of 3 columns: Identifier, As Of Date, and Score. Here's an example of the historical data:

Identifier	As Of Date	Score
A	1/1/2007	5
B	1/1/2007	7
C	1/1/2007	5
A	2/1/2007	4
B	2/1/2007	8

<tbody>
</tbody>

What I originally built out was a separate table of INDEX MATCH functions with the use of SMALL to pull the historical data for one specific identifier that was entered into a cell. For example, I would input A into a cell and a separate table would populate with:

As Of Date	Score
1/1/2007	5
2/1/2007	4

<tbody>
</tbody>

I already built this and now, the next step that I would like to take is to be able to enter multiple tickers and get all of the historical data of those tickers output into one table, so that I could see the average scores of the identifiers over time. I am a little stuck on how to approach this for it to be efficient and dynamic.

It is possible to use the INDEX MATCH SMALL table multiple times, but that does not seem feasible if I want to leave the number of identifiers up to the user.

Thanks for your help and any input at all is welcome!

 

[Eric W]

Here's one way to do it:
Excel 2010

	A	B	C	D	E	F	G
1	Identifier	As Of Date	Score		Identifier List	As Of Date	Score
2	A	1/1/2007	5		A	1/1/2007	5
3	B	1/1/2007	7		B	1/1/2007	7
4	C	1/1/2007	5			2/1/2007	4
5	A	2/1/2007	4			2/1/2007	8
6	B	2/1/2007	8

<tbody>
</tbody>

Sheet3​

Array Formulas Cell Formula F2 {=IFERROR(INDEX(B$1:B$10,SMALL(IF(ISERROR(MATCH($A$2:$A$10,$E$2:$E$5,0)),9E+99,ROW($A$2:$A$10)),ROW()-ROW(E$2)+1)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Enter the formula in F2, then copy the F2 cell and paste it down column F, and paste it into column G

In column E put the list of identifiers you want listed. Columns F and G will show the dates and scores. Upon looking at it, you may want another column with the identifier too. Let me know if you do.