# need a search option i cant find!

• 01-06-2009, 11:37 PM
Ckooz
need a search option i cant find!
Hello,

My Excel is dutch but i still will atach it! Im working on a sheet for a speeddate at my school. Now in page one there are the results of men. In page 2 the results of women. Now in page 3 are the results of both if they both have the same you have a match!! :D

Now i would like to have on page 4 all men (101-150) with behind it the numbers of the women they match....

If you can find the formula for this in english its easy for me to translate so please help me with this one!

greetings,

Frans
• 01-07-2009, 04:30 AM
DonkeyOte
Not entirely sure I'm following but if I've interpreted correctly I'd probably go for something along the lines of the following:

1 - Sheet Resultaat:
B2: =IF(AND(vrouwen!B2="x",mannen!B2="x"),COUNT(\$A2:A2),"")
copy down for all rows and across to column AY
AZ2:=COUNT(B2:AY2)
copy down for all rows (effectively = count of *matches* for given man)

2 - Sheet Resultaat mannen
Insert Row in Row 1 (ie existing row 1 becomes row 2)
In D1: "Match"
In B2: "Row"
In C2: "Matches"
In D2: =N(C2)+1
copy across to say J2
In B3: =MATCH(\$A3,Resultaat!\$A\$1:\$A\$100,0)
copy down for all Man ID's in A
In C3: =INDEX(Resultaat!\$AZ\$1:\$AZ\$100,\$B3,1)
copy down for all Man ID's in A

Now to populate matrix
In D3: =IF(\$C3<D\$2,"",INDEX(Resultaat!\$A\$1:\$AZ\$1,1,MATCH(D\$2,INDEX(Resultaat!\$A\$1:\$AZ\$100,\$B3,0),0)))
copy down for all Man ID's in A and across to J (ie to cater for max possible number of matches)

Hope that helps.

(note 2nd use of INDEX in the Matrix is volatile as it's being used to return a Range as opposed to a Value)