Find a name when there are duplicates in search columns.

J

jkmclean

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


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]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Formula for the Female
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]=INDEX($C$1:C62,SUMPRODUCT(($B$12:$B$62="F")*($F$12:$F$62=AC27)*(ROW($C$12:$C$62))))[/TD]
[/TR]
</tbody>[/TABLE]


Formula for the Male

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]=INDEX($C$1:C62,SUMPRODUCT(($B$12:$B$62="M")*($F$12:$F$62=AC27)*(ROW($C$12:$C$62))))[/TD]
[/TR]
</tbody>[/TABLE]


Regards Dante Amor
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
trying to use cells which have been copied and pasted values only.
Replies
2
Views
557
jkmclean
J
J
  • Question Question
Can you delay a calculation until required data is entered?
Replies
3
Views
631
jkmclean
J

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top