How do I create a ONE to MANY mapping cross-sheet formula/mapping?

Swapna Kasturi

I have a sheet called a "Catchall" which is the FULL list of projects being worked on in the company, and for every project on the list, there is a related OKR, start date, end date, health score etc

I need to be able to pull all related projects that share a COMMON objective into a different sheet. The goal being to be able to get an average score per objective. EXAMPLE in the case above, I would be able to create a new sheet in which by "querying" for the NEW Objective "OKR1", I can pull in Project A and Project B and their report card points, 10 and 10 and average them to get 10.

The reason I cannot use a report to do this is all the groupings don't reflect on the dash and I can't manipulate data in a report. How can I create this one to many mapping in sheets?

FYI tried VLOOKUP and JOIN COLLECT and didn't seem to work as I couldn't insert separate items under a single OKR into different rows.

Swapna Kasturi

Malaina Hudson

Hi, @Swapna Kasturi ,

Could you use AVERAGEIF to accomplish this? This wouldn't require that you list each project in your summary, just the criterion that you're matching to, and then AVERAGEIF would search for the condition and average the grades.

MH