I have a google sheet (Alignment form responses)(https://docs.google.com/spreadsheets/d/19nQ427RyA1_qyTyTNds2ZcYudLkFRicir901VudAJFQ/edit#gid=1879436751) consisting of 4 tabs(Sheet1,Sheet2,Comparison and Parameters).
Sheet 1: consists of form responses from both auditor and ops. Sheet 2: consists of form responses from only the auditor Comparison: Here the form responses of both the auditor and the ops is compared to check which parameters are aligning and a certain weightage is given to both of them Parameters: Consisting of parameters sub-parameters and it's respective weightages (Consider only the sub-parameters and it's weightages)
Condition:
If the auditor has selected 2 sub-parameters and the ops has selected only one, and that one sub-parameter matches with one of the sub-parameters submitted by the auditor then the score for both auditor and ops should be given equal score, since one of the sub-parameters match.
For example: In Sheet 1:
Auditor filled as : sub-parameter 1 (weightage:100), sub-parameter 2 (weightage:50) Ops Filled as : sub-parameter 2 (weightage:50)
In Sheet 2: Auditor Filled as : sub-parameter 1 (weightage:100), sub-parameter 2 (weightage:50)
In Comparison tab: Scores for both should be 50% as one sub-parameter is matching
Requirement:
Need a google sheets formula for the above condition
I tried to compare the responses in sheet1 and sheet 2 in the comparison tab to get the score but I wasn't able to get a breakthrough.
If anyone is trying to access and try to get an answer kindly ping me in hangouts at ([email protected])
Use the below formula to you can achieve the condition: