I am trying to conclude a cell value from 2 categories having numerical inputs derived from the result of a boolean output. For example, I need to evaluate the numerical target of a particular assignment type based on whether the week is hectic or not. For example if the week is hectic and the assignment type is TOC then there will be a target value say 40 while if the week is not hectic and the assignment is TOC the target will be 60. Similarly, for S2Rs assignment the value for hectic and non hectic weeks are respectively 35 and 50. I have created separate lists of target/assignment type and hectic week (Y/N) and also for the needed value to be picked for two list of different targets. I am using the following statement.

=IF(AND(F5=’Data Validation’!$Q$3,’Targets vs actual work’!D5=’Data Validation’!$Y$3),’Data Validation’!$S$3,’Data Validation’!$T$3)

but the value is only coming for hectic week.