Cross referencing two tables to produce a list of values

[Vardellic]

I've been working on this for a while and can't quite figure it out. Looking for some help where I have two tables (a project list and group list). Each table identifies the benefits of the projects (may be multiple benefits) and the benefits of the group (again, may be multiple benefits). The benefits list (columns) are identical in both tables but the rows may vary. Therefore, the size of the tables are different.

The desired outcome is to identify a list of groups with the same benefits as a project (i.e. any project that has a "yes" listed for a specific benefit would then be cross referenced with the groups that have a yes listed for the same benefit and return all possible matching groups for all benefits. I've attached an image with both tables and the desired outcome for reference.

I've tried using index and match-like functions to return a list of "yes" benefit values for each table but I'm not sure where to go from there.

Thanks for any help you can provide.

 

[igold]

Hi Vardellic- Welcome to the MrExcel Forum.
I have been looking at your picture of the data and desired outcome. First and not necessarily important is that I don't think your data is in Tables as stated, it looks like two ranges of data. That said, and more importantly is that I am looking at the data and unless I am missing something, I do not see any way (from what is being shown) that ties the two groups of data in such a way as to produce the desired result. There is no connection that I see.
How do I know that Project A has four groups (two of which are soil) and Project C has only one group.

 

[Vardellic]

Hi igold. You are correct. They are technically not tables but ranges of data.

The desired result is something I manually typed up to illustrate what would be the result. What I am looking for is a way to produce that desired result with functions that relate the two ranges of data.

 

[igold]

Not without some sort of identifiable connection between the two sets of data, which I do not see. Can you provide the criteria that you used to manually create the resultant set of data.

 

[Vardellic]

Yes of course.

More specifically, I need to evaluate each project to determine which benefits it has. Project A has benefits of Water, Soil, and Benefit N because the "Yes" value is listed. I am using the "Yes" value to link the two sets of data

Then, based on the project benefits that have a "Yes" value, I want to list any groups that also have a "Yes" value for the same benefits. So for the water benefit (which was a "Yes" for Project A), Group 1 is "Yes" and thus would be listed under Project A. For the Soil benefit, Group 2 and Group 3 have "Yes" so they would be added to the list. For Benefit N, Group 2 and Group N have "Yes" so they would be added to the list of groups under Project A. Finally, the list would then be checked and any dupliate groups would be removed. For Project A, Group 2 is listed twice, once for the Soil benefit and once for the Benefit N. So the second occurance of Group 2 is removed from the final output list for Project A.

This process would then be repeated for each subsequent project in the project range of data.