Hello,
I am trying to string 18 IF worsheet Functions together in order to evaluate input data from a worskheet in order to establish a corresponding scope priority in column V. For example if given a "H" in column S, a "H" in column T, and a "L" in Column U using the line 285 from the look up chart below the assigned "Scope Priority" should be II. The lookup chart is preferred to accomodate easy changes rather than multiple change to countless formulas.
I have written the IF Function:
=IF(Q8=$S$270,IF(R8=$T$270,IF(S8=$U$270,$V$270,IF(Q8=$S$271,IF(R8=$T$271,IF(S8=$U$271,$V$271))))))
Q8, R8, S8, refer to the input data worksheet cells that are being analyzed in order to asses the scope priority.
This function works fine but is limited to the first two lines of the look up chart. As soon as I attempt to extend the IF Function for the remaining 16 lines of possibilities, it comes up as an error.
Does anyone have any suggestions?
S T U V
Materiality Risk Scope Priority
Line Ttl
270 L L L III
271 L L M III
272 L L H III
273 L M L III
274 L M M III
275 L M H III
276 L H L III
277 L H M III
278 L H H III
279 M M L III
280 M M M II
281 M M H I
282 M H L II
283 M H M I
284 M H H I
285 H H L II
286 H H M I
287 H H H I
Thank You in advance for your time.
John B.
I am trying to string 18 IF worsheet Functions together in order to evaluate input data from a worskheet in order to establish a corresponding scope priority in column V. For example if given a "H" in column S, a "H" in column T, and a "L" in Column U using the line 285 from the look up chart below the assigned "Scope Priority" should be II. The lookup chart is preferred to accomodate easy changes rather than multiple change to countless formulas.
I have written the IF Function:
=IF(Q8=$S$270,IF(R8=$T$270,IF(S8=$U$270,$V$270,IF(Q8=$S$271,IF(R8=$T$271,IF(S8=$U$271,$V$271))))))
Q8, R8, S8, refer to the input data worksheet cells that are being analyzed in order to asses the scope priority.
This function works fine but is limited to the first two lines of the look up chart. As soon as I attempt to extend the IF Function for the remaining 16 lines of possibilities, it comes up as an error.
Does anyone have any suggestions?
S T U V
Materiality Risk Scope Priority
Line Ttl
270 L L L III
271 L L M III
272 L L H III
273 L M L III
274 L M M III
275 L M H III
276 L H L III
277 L H M III
278 L H H III
279 M M L III
280 M M M II
281 M M H I
282 M H L II
283 M H M I
284 M H H I
285 H H L II
286 H H M I
287 H H H I
Thank You in advance for your time.
John B.