Forum Discussion

catherine9910's avatar
catherine9910
Brass Contributor
Apr 13, 2022

Multiple Criteria

More formula help, please.   The problem I am having is when I use a formula, I need it to look up and return a value when the 1st part of the formula changes.  For example, if I want A2 on anothe...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Apr 14, 2022

catherine9910 

=CHOOSE(IF($A2=1234,1,IF($A2=5678,2,IF($A2=9012,3,IF($A2=3456,4,IF($A2=7890,5))))),IF(NOT(ISNA(VLOOKUP(F$1,'Audit Sheets'!$B$11:$E$31,4,FALSE))),VLOOKUP(F$1,'Audit Sheets'!$B$11:$E$31,4,FALSE),VLOOKUP(F$1,'Audit Sheets'!$I$11:$L$31,4,FALSE)),,IF(NOT(ISNA(VLOOKUP(F$1,'Audit Sheets'!$B$49:$E$69,4,FALSE))),VLOOKUP(F$1,'Audit Sheets'!$B$49:$E$69,4,FALSE),VLOOKUP(F$1,'Audit Sheets'!$I$49:$L$69,4,FALSE)),,IF(NOT(ISNA(VLOOKUP(F$1,'Audit Sheets'!$B$87:$E$107,4,FALSE))),VLOOKUP(F$1,'Audit Sheets'!$B$87:$E$107,4,FALSE),VLOOKUP(F$1,'Audit Sheets'!$I$87:$L$107,4,FALSE)))

Maybe with this formula which seems to work in the attached file. However there has to be an easier solution.

catherine9910's avatar
catherine9910
Brass Contributor
Apr 15, 2022
I have even tried to add an offset function too, so once the OP# is found, then the formula would offset to the required area. Just a thought, not sure if it could work?
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 15, 2022

    catherine9910 

    =IF(NOT(ISNA(VLOOKUP(F$1,OFFSET('Audit Sheets'!$G$3,MATCH('Voice Stats by Start Groups'!$A2,'Audit Sheets'!$G$3:$G$79,0)+7,-5,21,4),4,FALSE))),VLOOKUP(F$1,OFFSET('Audit Sheets'!$G$3,MATCH('Voice Stats by Start Groups'!$A2,'Audit Sheets'!$G$3:$G$79,0)+7,-5,21,4),4,FALSE),VLOOKUP(F$1,OFFSET('Audit Sheets'!$G$3,MATCH('Voice Stats by Start Groups'!$A2,'Audit Sheets'!$G$3:$G$79,0)+7,2,21,4),4,FALSE))

    OFFSET is probably the best choice to do this. The OFFSET formula is easier and the other formula uses CHOOSE which is limited to 254 values as far as i know. 

    • catherine9910's avatar
      catherine9910
      Brass Contributor
      Apr 20, 2022

      OliverScheurich I have another part that I need help with. I am not quite good enough to understand your formula to adjust it to what I need. I have what I need help with on the Auditor Sheets tab. Thank you.

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Apr 20, 2022

        catherine9910 

        =AVERAGEIFS(AE4:AJ4,AE4:AJ4,">"&0)

        *You can try this formula to calculate the average for all cells with values greater than zero. The formula is in cell AK4 of the "Audit Sheets". I had to adjust the layout of the data to apply this formula.

         

        =AVERAGE(OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+12,-2,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+20,-2,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+28,-2,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+12,5,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+20,5,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+28,5,1,1))

        **You can try this formula (which is in cell S4 of the "Audit Sheets") to automatically caluculate the average for every employee. In order to complete the chart you can add the remaining employee numbers in range R7:R1200 and copy the formula across the range S4:S1200.

Resources