Indirect combined with countif for weighted matching array with multiple criteria

Hi,

I am embarking on a little project at the moment that will involve 'matching' users based on various criteria they supply. I have started from scratch and got to the first sticking point that I can't seem to figure out. So far I have managed to produce an array that matches each user with each other user across some of the criteria to produce a 'weight' number, but I have not managed to include all the criteria yet. I am also tussling with the problem of dragging formulae horizontally but needing references to increase vertically. I have so far managed to produce something that works (but not perfectly) using the Indirect and Column functions.

Now the main difficulty I have at the moment is each user states 2 'interests': a Primary and a Secondary. So what I need to do is check if a user's Primary interest is the same as any other user's Primary or Secondary interests. If two users have a match there will be a multiplier>1 to increase the weight on that match, if not, there will be a multiplier <1 to decrease the weight of that match.

To simplify, I have tried to make a representative sample, and I've used colours for the interests. The data is contained in sheet2 called 'data' and the formula will be in sheet1 called 'match'.

In the match table, I have mocked up the results with 1's and 0's (to just go binary for now). So in cell B2, I want to ask what is the Primary interest of person ID 1 and is that interest contained in person ID 2's primary or secondary interests. In this case, yes, person 1's primary interest is red and that is person 2's Primary interest also. As I drag down, I will compare the next person down, back with person 1 each time. However, when I drag across I will then change the index user. So in cell C3 I want to be looking at the primary interest of person 2, and see if person 3 has that interest in their primary or secondary. Then as I drag that column down, I will always be comparing back to person 2.

Now in my full spreadsheet, I have got other matches working, however, I have not go them working in the right part of the array. At the moment I have got the right values for the other criteria to match but they are appearing in the top left of the array: (yellow area).

data pic.png

Unfortunately then this does not really line up correctly with the IDs in column A that are being matched. Ideally as I have mocked up in my sample results, I want the data to be in the bottom left, as this lines up with the IDs in column A that are being compared correctly and will not then require any further manipulation afterwards.

I hope I have managed to make some sense so far, I am finding this quite difficult to describe. Possibly giving a sense of the formulas might help...
So I think that I need something along the lines of:

COUNTIF(INDIRECT("'Data'!C"&COLUMN()+ROW()-2&":"&"'Data'!D"&COLUMN()+ROW()-2),INDIRECT("'Data'!C"&COLUMN()))

This was my attempt, however if I could get it correct, I think it would put the data in the top left of the array, not the bottom left. But there is something wrong with the syntax (or more) in my formula.

In my full spreadsheet, here is part of my key formula, that I think is working:
IF(INDIRECT("'Data'!F"&COLUMN())=INDIRECT("'Data'!F"&COLUMN()+1+ROW()-2),1,0)

This compares user criteria in column F, if they are equal, I multiply the current weighting by 1, if not I multiple by 0 as this is a hard criteria that must be met. When I drag this formula across the array it works comparing each user with each other user (although as mentioned above, the data is not showing in the best part of the array, top left rather than bottom left).

If anyone manages to understand what I'm talking about, help would be much appreciated. I will try my best to clarify anything further if and when queries come in. I attach the sample data, and I have also attached my main spreadsheet (all just made up user data), if you want to try and get the context...bear in mind it is a work in progress..NB in the lists sheet of my main spreadsheet in the table on the right you can see the weightings I have decided on thus far for the different criteria, I have filled green the criteria I have so far managed to add into the formula.

Thanks,
Tom

Oh and PS if and when I get around this problem, my next is how to incorporate the 'experience column', I need to check if users primary or secondary interests are contained in other users 'experiences' also, however, there may be multiple experiences in the same cell, so I need a countif or something that is looking for 'contains text' rather than 'equal to'....

FYI I have just managed a workaround that uses 3 OR functions, 1 that does a countif (with just a 1 cell range) and 2 that are just testing if single cells equal single cells. Therefore I don't have to solve the syntax of using INDEX with COLUMNS and ROWS inside a countif when representing a range...though that is still a problem I would like to know the answer to (assuming there is one..).