Hi there,
I am trying to match a range of assessors to projects, so that a person with the most relevant expertise can be assigned to review a given project. I have been trailing a range of array formulas using vlookup and index/match combos but I am not getting the answer I need. It seems that the formulas are finding only the first row or column with the matching information and then returning that result.
- I have a list of people and a list of keywords/categories aligned to their expertise. Where that person has expertise in an area I have marked a "Y" in the corresponding cell in the array. See the Assessors worksheet in the attached XLS file.
- I also have a range of projects, of which I have extracted a range of keywords from a description. Keywords correspond to the expertise areas assigned to the assessors. See the Keywords Extract worksheet in the attached XLS file.
I would like to count the number of times a persons expertise categories/keywords matches a given projects categories/keywords.
- As an example for Person 1; the formula should calculate a value of 1 on the line for Project_46 (cell O3 in the download), a value of 4 on the line for Project_125 (cell O7 in the download) and a value of zero for Project_36 (cell O45 in the download)
I sincerely appreciate your help on this !!
Cheers,
Bookmarks