Biggest in list and matching

**medman** · 02-13-2011, 04:26 PM

I actually have two questions I would appreciate any help I can get with. I am working on a excel spreadsheet for my volunteer organization. The purpose is to be a survey of different personal traits in different categories that then are used to suggest possible jobs within the organization that the individual may be specially gifted in doing.
Here are my problems:
1. I have been able to figure out how to display the top 3 values, from scores related to traits. However, if two traits get the same score, I only get the name of one displayed. How can I get it to display both.

2. How do I get the spreadsheet to match and then display a set of jobs that match the top traits. I have a top three trait list that I want match up against a trait list for each job then have the spreadsheet display the job name and description in order of best match.

I have included a sample spreadsheet with my problems as a example.

I appreciate any help anyone can give me with this. Thanks so much.

**nimrod** · 02-13-2011, 05:44 PM

QUETION (1) sol'n

The formula is quite complex so I suggest that you divide the formulas into two columns ... and Hide the secondary column if you'd like.
Below I've provided you with the formulas for cells G2:G4 & cells H2:H4

G2: =INDEX($A$1:$A$20,H2,1) H2: = MATCH(D2,$B$1:$B$20,0)

G3: =INDEX($A$1:$A$20,H3,1) H3: =IF(D2=D3, MATCH(D3,INDIRECT("B" &H2+1 & ":B20"),0) + H2,MATCH(D3,$B$1:$B$20,0))

G4: =INDEX($A$1:$A$20,H4,1) H4: =IF(D3=D4, MATCH(D4,INDIRECT("B" &H3+1 & ":B20"),0) + H3,MATCH(D4,$B$1:$B$20,0))