Forum Discussion
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...
=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.
- 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?
=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.
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.
- It does work until you have 1200 employees to add. This is at least a start.
