Find a name when there are duplicates in search columns.

[jkmclean]

Gender	Name	hcp	HGS	HSS	HGH	HSH	gms	Ave	Total Pins			HGS Left High	HGS Right High	HGH Left High	HGH Right High	HSS Left High	HSS Right High	HSH Left high	HSH Right High	Most Improved
F	Betty McLean	88	128	337	216.2	601.600	3	112.333	337			128	0	216.2	0	337	0	601.6	0	0		High Game Scratch - Men		High Series Scratch - Men	High Game Handcap - Men
M	Jim Karasimos	79	129	366	208.2	603.600	3	122	366			129	0	208.2	0	366	0	603.6	0	0		Ken Killen	218		Ken Killen	536	Wayne Willfang	260.7
M	Jack StevenHaagen	86	139	343	225.4	602.200	3	114.333	343			139	0	225.4	0	343	0	602.2	0	0		Gary Tisdale	203		Gary Tisdale	508	Mary Birrell	250.6
M	John McLean	39	182	499	221.6	617.800	3	166.333	499			182	0	221.6	0	499	0	617.8	0	0		Gerry Revelle	202		John McLean	499	Gary Corkey	233.5
F	Verna Mortensen	60	162	431	222.3	611.900	3	143.667	431			162	0	222.3	0	431	0	611.9	0	0		High Series Handicap - Men	High Average - Men	Most Improved - Men
M	Ken Killen	28	218	536	246.8	622.400	3	178.667	536			218	0	246.8	0	536	0	622.4	0	0		Ken Killen	622.4		Ken Killen	178.6667	Ken Killen	0
F	Joyce Banks-Stevenhaagen	62	172	425	234.1	611.300	3	141.667	425			172	0	234.1	0	425	0	611.3	0	0		Gary Tisdale	618.7		Gary Tisdale	169.3333	Ken Killen	0
M	Alex McKinnon	#####	0	0	0	0.000	0	#DIV/0!	0			0	0	0	0	0	0	0	0	#DIV/0!		John McLean	617.8		John McLean	166.3333	Ken Killen	0
F	Linda Carter	76	142	377	218.5	606.500	3	125.667	377			142	0	218.5	0	377	0	606.5	0	0
F	Patricia Renaud	77	128	374	205.4	606.200	3	124.667	374			128	0	205.4	0	374	0	606.2	0	0		High Game Scratch - Women	High Series Scratch - Women	High Game Handicap - Women
M	Dan Murphy	108	113	271	221	595.000	3	90.3333	271			113	0	221	0	271	0	595	0	0		Joyce Banks-Stevenhaagen	172		Verna Mortensen	431	Joyce Banks-Stevenhaagen	234.1
F	Evelyn Farrah	81	137	359	218.9	604.700	3	119.667	359			137	0	218.9	0	359	0	604.7	0	0		Verna Mortensen	162		Joyce Banks-Stevenhaagen	425	Diane Clarke	226.9
																						Theresa Reynolds	157		Kerry Hietala	406	Jack StevenHaagen	225.4
M	Gerry Revelle	48	202	469	250.6	614.800	3	156.333	469			202	0	250.6	0	469	0	614.8	0	0

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I am trying to find the female which has the 406 value in the HSS column. The problem is the formula finds the first 406 value belonging to a male.
I am using the following formula.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX($C$12:C62,MATCH(AC27,$F$12:$F$60,0))[/FONT]

 

[DanteAmor]

Formula for the Female

=INDEX($C$1:C62,SUMPRODUCT(($B$12:$B$62="F")*($F$12:$F$62=AC27)*(ROW($C$12:$C$62))))

<tbody>
</tbody>

Formula for the Male

=INDEX($C$1:C62,SUMPRODUCT(($B$12:$B$62="M")*($F$12:$F$62=AC27)*(ROW($C$12:$C$62))))

<tbody>
</tbody>

Regards Dante Amor

 

[jkmclean]

Thanks a lot. This works. I am not sure what sumproduct does but will look it up and try to understand.
I am a total novice and self taught so really appreciate the help.

 

[DanteAmor]

The sumproduct function, can add the multiplication of arrays, also can be used as a array formula to obtain the results of several conditions. In your case, look for data equal to "F" and also be equal to AC27; in the last array you get the row number where the 2 matches were true; finally, with the index function, the data of the column "C" is obtained according to the row obtained with the sumproduct function.
I am glad to help you.