Find with multiple conditions item in Table List Google Sheets

0
votes

I have a list with agents results + Rules of priority looking like this enter image description here

What should happen: In the Agents Results list, the formula should look for the line where all the rules are met. And then return Agent name. Like in the first rule (the Hero's Name is hardcoded).

Agent 1 has Quality Result more than 99% and Productivity Result more than 115%. So he is in the column Hero's Name.

My attempts to solve the task.

  1. To find agent's Name use INDEX function
  2. To find the row argument for INDEX use MATCH function

The obstacles I've met

  1. INDEX function returns only one result. What to do if several rows meet the Rule of Priority?
  2. I can't use numeric range from/to in MATCH function to find row due to Rule of Priority that includes ranges (like Quality 99+ means all the values from 99.01% to 100%)
  3. I can't use multiple conditions in MATCH to find the row that meets both Quality and Productivity conditions from Rules of priority

Please, help. I would be grateful for ideas on how this can be solved.

2
functiongoogle-sheetsgoogle-sheets-formula
share a copy of your sheet - player0
Your sheet is protected. Please share it so anyone can EDIT it, as described in the link. That is why you only share a sample/copy of your sheet, with non-sensitive data. support.google.com/docs/thread/3808684?hl=en - kirkg13
@player0 Here it is docs.google.com/spreadsheets/d/… - Vel Green
@kirkg13 thanks a lot! Corrected. - Vel Green

2 Answers

3
votes

try:

=IFERROR(JOIN(", "; FiLTER(C5:C; A5:A >= 98%; 
                                 A5:A <  99%; 
                                 B5:B >= 110%; 
                                 B5:B <  115%)); "no one")

enter image description here

2
votes

You should use filter function: To have all agents that meet conditions quality > 99 and productivity >115 % you can make a function: c - agents b - productivity a - quality

=filter(c5:c,a5:a>0.99,b5:b>1.15)

this will produce you a column of agents.

For more conditions (ranges of productivity, quality), you just add more conditions into filter fuction: for productivity between 110 and 115 and quality between 98 and 99 you write:

=filter(c5:c,a5:a>0.98,a5:a<0.99,b5:b<1.15,b5:b>1.1)