Return corresponding values based off of Data Validation List

[imheadsup]

Hello. I want to have a drop-down list with selections and based off of the selection in that list, other cells will populate the corresponding row values associated with that selection. For example, if I select Virginia, the table will return all data for that selection.

Raw data:

State	Universities	Tuition	City	Student Population
Virginia	Virginia Tech	$60,000	Blacksburg	1000
Virginia	University of VA	$80,000	Charlottesville	2000
Virginia	James Madison University	$40,000	Harrisonburg	3000
Maryland	University of MD	$70,000	College Park	4000
Maryland	Towson	$35,000	Baltimore	5000
North Carolina	UNC	$60,000	Chapel Hill	6000
North Carolina	Duke	$65,000	Durham	7000
North Carolina	ECU	$55,000	Greenville	8000

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

Select State:	Virginia (Drop down list)
State	Universities	Tuition	City	Student Population
Virginia	Virginia Tech	$60,000	Blacksburg	1000
Virginia	University of VA	$80,000	Charlottesville	2000
Virginia	James Madison University	$40,000	Harrisonburg	3000

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[DRSteele]

Welcome to the forum.

This formula will extract all the matching records for all the fields for the Data Validation selection in I2.

Copy I5 across and down. You must copy down a sufficient number of rows to cover the number of matches there might be, a number shown in J2.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	State	Universities	Tuition	City	Student Population
2	Maryland	Towson	$35,000	Baltimore	5000			Select State:	Virginia	3
3	Virginia	James Madison University	$40,000	Harrisonburg	3000
4	North Carolina	ECU	$55,000	Greenville	8000				State	Universities	Tuition	City	Student Population
5	Virginia	Virginia Tech	$60,000	Blacksburg	1000				Virginia	James Madison University	40000	Harrisonburg	3000
6	North Carolina	UNC	$60,000	Chapel Hill	6000				Virginia	Virginia Tech	60000	Blacksburg	1000
7	North Carolina	Duke	$65,000	Durham	7000				Virginia	University of VA	80000	Charlottesville	2000
8	Maryland	University of MD	$70,000	College Park	4000
9	Virginia	University of VA	$80,000	Charlottesville	2000
Sheet73

Cell Formulas
Range	Formula
J2	=COUNTIFS($A$2:$A$9,$I$2)
I5	=IF($J$2>=ROWS($I$5:I5),INDEX(A$2:A$9,1/AGGREGATE(14,6,1/(($A$2:$A$9=$I$2)*(ROW($A$2:$A$9)-ROW($A$1))),IF($J$2>=ROWS($I$5:I5),ROWS($I$5:I5),""))),"")

 

[imheadsup]

This worked perfectly. I just need to do a little manipulation to make it work exactly how I need it to for my table and I'll be set to go. Thank you.

 

[DRSteele]

Terrific...I'm glad it helped. You're welcome.

 

[imheadsup]

Question for you:

I'm having two problems with this now.

1. I have a counter, just like you said, but after about 30 rows of data, I'm getting a #num ! error. Any ideas why?

2. When I select the first value in my drop down list, all respective rows show up (with the exception of anything past row# 30). If I select any other values from my drop down list, the only the first corresponding row data is displayed.

Is there a way I can attach my file so you can see what's happening?