Looking up lab codes: Returning specific cell value based on combination of TRUE cells

Hi there,

Take a look at the attached version of your workbook and see if it does what you need. It uses the following formula in Cell H9 of worksheet "Sheet2":

=A9  *  IF(B9 = "YES",  B$1,  NOT(B$1))  *  IF(C9 = "YES",  B$2,  NOT(B$2))  *  IF(D9 = "YES",  B$3,  NOT(B$3))  *  IF(E9 = "YES",  B$4,  NOT(B$4))  *  IF(F9 = "YES",  B$5,  NOT(B$5))

This formula is copied downwards as far as Cell H14.

Cell B23 of worksheet "Sheet1" contains the following formula:

=IF(MAX(Sheet2!H9:H14) = 0,  "Not Defined",  MAX(Sheet2!H9:H14))

It should not be too difficult for you to expand the above logic to cater for additional texts and combinations.


Hope this helps - please let me know how you get on.

Regards,

Greg M