How to use drop down list to return entire table of data?

Hello,

I am trying to use a drop down list to return a table of data. I have monthly returns for three stock market indices (S&P 500, MSCI EAFE, MSCI EM), and I want to be able to select one of those indices from the list in a drop down menu, which will then return the monthly dates and returns columns. Please see my sample data which has the 4 columns: Date, S&P 500, MSCI EAFE, MSCI EM:

Date S&P 500 MSCI EAFE MSCI EM
12/30/2005 1.00% 1.00% 1.00%
1/31/2006 2.00% 2.00% 2.00%
2/28/2006 3.00% 3.00% 3.00%
3/31/2006 1.00% 1.00% 1.00%
4/28/2006 2.00% 2.00% 2.00%
5/31/2006 3.00% 3.00% 3.00%

My goal is to select "S&P 500" from my drop down menu, and it will return all data in Columns A (Date column) and B (S&P 500 Returns column), which would be six months of returns with their respective dates.

I appreciate any help that somebody can provide. I went through forums for a long time but was unable to find my exact issue.

Thanks.

Hi, welcome to the forum

Im not sure how you would ID what you need, when the criteria seems to be a table heading?

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Set up the date range by giving it a name for each of the other columns. I assigned names that you will see in the Name Manager.
Assign Names to each column of data

Create the drop-down listing. I just listed the names in the Data Validation that matched what I had made for the data columns but not the Date column. The date column is obtained by using =INDIRECT(I2&"D") which is the name that the drop-down list returns and adds a D to it. This will match the names that I assigned to the Date column.

The enclosed file should be self explanatory (at least better than I can describe with words)

Deleted file

In my first post I had misunderstood what the column names should be. I think that I have corrected that.

Sooner or later I will get this right

Third times a charm...I hope.

Forgot to add: Make the area for the returns large enough to return the largest amount of data. (Select the area and array enter the formulae given) You will get error values for unused cells. You can hide these errors using Conditional formatting.

Maybe this...

Data Range

	A	B	C	D	E	F	G
1	Date	S&P 500	MSCI EAFE	MSCI EM	------	Date	S&P 500
2	12/30/2005	1.00%	1.00%	1.00%		12/30/2005	1.00%
3	1/31/2006	2.00%	2.00%	2.00%		1/31/2006	2.00%
4	2/28/2006	3.00%	3.00%	3.00%		2/28/2006	3.00%
5	3/31/2006	1.00%	1.00%	1.00%		3/31/2006	1.00%
6	4/28/2006	2.00%	2.00%	2.00%		4/28/2006	2.00%
7	5/31/2006	3.00%	3.00%	3.00%		5/31/2006	3.00%

G1 is the drop down list.

This array formula** entered in F2:

=IFERROR(INDEX(A:A,SMALL(IF(A$2:A$7<>"",ROW(A$2:A$7)),ROWS(F$2:F2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

This formula entered in G2:

=IF(F2="","",INDEX(B$2:D$7,ROWS(G$2:G2),MATCH(G$1,B$1:D$1,0)))

Format as Percentage 2 decimal places.

Select F2:G2 and copy down until you get blanks.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

You're welcome. Thanks for the feedback!

You're welcome and thank you for the feedback.

Good luck.