Lookup all possible surnames based on first name in another cell - for DV list

[Mr__P]

I have a table containing education assessment data and need to use the data to analyse a child's data on another worksheet. So far I have set up 3 drop down boxes that contain 'First Name', 'Surname', 'Class'.

I have set up these to look at the fields in the table but I would like it so that when I select a 'First Name', the 'Surname' drop down shows only the possible surnames that match the first name rather than all the surnames in the table.

The worksheet successfully looks up the correct data based on matching all three drop-downs to records in the table so this isn't the issue. I just want to make it more user friendly.

Many thanks.

 

[AlanY]

try this

data validation for A2, =$F$2:$F$500
and B2, =$G$2:$G$500

Excel 2012
	A	B	C	D	E	F	G
1	First Name	Surname		First Name	Surname
2	Alan	Name1		Alan	Name1	Alan	Name1
3				Alan	Name2	Steve	Name2
4				Alan	Name3	Keith	Name3
5				Alan	Name4		Name4
6				Steve	Name5
7				Steve	Name6
8				Steve	Name7
9				Keith	Name8
10				Keith	Name9
11				Keith	Name10
12				Keith	Name11
13				Keith	Name12
6

Cell Formulas
Range	Formula
F2	{=IFERROR(INDEX($D$2:$D$13, MATCH(0, COUNTIF($F$1:F1, $D$2:$D$13), 0)),"")}
G2	{=IFERROR(INDEX($E$2:$E$500,SMALL(IF($D$2:$D$500=$A$2,ROW($E$2:$E$500)-1),ROW(E1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.