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 another sheet to look up OP# 2 in column 2, then look up Date 2 and return Average 2 within that yellow box. Or if I want it to look up OP# 3 then only return the average in the blue box.

When I write out the formula I don't know how to keep it from trying to look up all of Date 2's (in that column) and returning all of the Average 2's (in that column).

I need a formula that will look up only in that colored block based on the OP# I want to look up. I hope that makes sense.

 

 

Formulas and Functions

8 Replies

  • 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 15, 2022
      It does work until you have 1200 employees to add. This is at least a start.

Resources