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