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

I work in a small medical clinic and manually looking up lab codes is a time sink for my team.

I'm attempting to make an excel lookup tool for finding the correct lab test code based on the required components. There are several components that can be ordered in any combination and each combination has a specific code. Some component examples are Chemistry, Thyroid Screen, Fecal Analysis, Urinalysis, and UPC Ratio. I'm using linked checkboxes (form controls).

Example of the functionality I'm aiming for:

If Chemistry and Thyroid Screen and true, then display code 105.
If Thyroid Screen and Urinalysis and UPC Ratio are true, then display code 115.

Honestly, all I have so far are my checkboxes linked to their respective cells and a reference table of components per code. I've been doing some research, youtubing, etc, but I'm stuck staring at the screen just totally unsure where to start with the multiple logic functions

Workbook example attached.

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

What an elegant solution!!! You're a genius, thank you so much. You just made a huge daily headache disappear, a group of grumpy lab techs thanks you

Hi again,

Many thanks for your very prompt feedback.

What more could anyone want than the thanks of a group of grumpy lab techs?!?!?

You're welcome - glad I was able to help.

Regards,

Greg M