Returning a result based on 1 of 4 other cells

[Tinglytangly]

I need to clearly output a result in a cell based on the outcome of formulae in four other cells.

I'm working with a doctor's surgery to establish which clinician needs to deal with a patient. To do this, I've been using If functions to check if cells are blank or not blank. The formulae for these work, but the results are unclear at a glance. 

I want to have Cell Prime look at Cell 1, if Cell 1 has Result 1, Cell Prime displays Result 1.  If it doesn't, move on to Cell 2. If Cell 2 has Result 2, Cell Prime displays Result 2, and so on for the four cells. There will only ever be one of the four cells which has a result other than 'null'.

This seems like something I should be able to do with nested if functions, but I've been striking out as the logical tests only allow for two outputs - true and false. I tried using a sequential If statement, and it works for Cell 1 - but not 2, 3 or 4.

This is the formula I tried:

=IF(AL4="HCA","HCA",IF(AN4="HCA_Pharm","HCA_Pharm",IF(AM4="HCA_GP","HCA_GP",IF(AO4="Nurse","Nurse",""))))

I could use an IF function to filter the cells in pairs - have it look at Cell 1 and Cell 2, then output the result of that, then have an IF look at Cells 3 & 4, output the result of that, then have an IF statement to compare the two results, and output THAT... but there must be a better way of doing it. 

-edit-

quick edit to make this clearer - Cell 1, 2, 3 and 4 contain formulae so we can't use ISBLANK or Coalesce. Screenshot:

[#2]

@Tinglytangly 

=IF(AL4="HCA","HCA",IF(AN4="HCA_Pharm","HCA_Pharm",IF(AM4="HCA_GP","HCA_GP",IF(AO4="Nurse","Nurse",""))))

You can try this formula.

[#3]

@Tinglytangly 

If function 

=IF(NOT(ISBLANK(A1)), A1, IF(NOT(ISBLANK(B1)), B1, IF(NOT(ISBLANK(C1)), C1, IF(NOT(ISBLANK(D1)), D1, ""))))

In this formula, each IF function checks if the corresponding cell (A1, B1, C1, or D1) is not blank using the ISBLANK function. If a cell is not blank, it returns the value of that cell. If a cell is blank, it moves on to the next IF statement until it finds a non-blank cell.

OR

= COALESCE(A1:D1)

Coalesce

The Coalesce function evaluates its arguments in order and returns the first value that isn't blank or an empty string. Use this function to replace a blank value or empty string with a different value but leave non-blank and non-empty string values unchanged. If all the arguments are blank or empty strings then the function returns blank, making Coalesce a good way to convert empty strings to blank values.

Coalesce( value1, value2 ) is the more concise equivalent of If( Not IsBlank( value1 ), value1, Not IsBlank( value2 ), value2 ) and doesn't require value1 and value2 to be evaluated twice. The If function returns blank if there is no "else" formula as is the case here.

All arguments to Coalesce must be of the same type; for example, you can't mix numbers with text strings. The return value from Coalesce is of this common type.

[#4]

Hiya,
Neither of these work because the cells have formulae in them, so even if I set them to return "" as the null result, not(isblank) still sees the cells as not blank because they have formulae in them. Same problem with Coalesce :(

[#5]

@OliverScheurich 

My sheet really isn't liking that, but let me go and have a look at WENN functions to see what's tripping that up...

@OliverScheurich

my transcription of your suggestion:

=WENN(AL4=”HCA”;”HCA”;WENN(AN4=”HCA_Pharm”;”HCA_Pharm”;WENN(AM4=”HCA_GP”;”HCA_GP”;WENN(AO4=”Nurse”;”Nurse”;””))))

[#6]

@Tinglytangly 

Do you work with german Excel? Then use this formua:

=WENN(AL4="HCA";"HCA";WENN(AN4="HCA_Pharm";"HCA_Pharm";WENN(AM4="HCA_GP";"HCA_GP";WENN(AO4="Nurse";"Nurse";""))))

Please note that ” from your post and " from the above formula are different.

If you work with english Excel you can try this:

=IF(AL4="HCA","HCA",IF(AN4="HCA_Pharm","HCA_Pharm",IF(AM4="HCA_GP","HCA_GP",IF(AO4="Nurse","Nurse",""))))

With spanish Excel you could try this:

=SI(AL4="HCA";"HCA";SI(AN4="HCA_Pharm";"HCA_Pharm";SI(AM4="HCA_GP";"HCA_GP";SI(AO4="Nurse";"Nurse";""))))

[#7]

@OliverScheurich 

English Excel - sorry, I should have made that clearer.

The suggestion:

=IF(AL4="HCA","HCA",IF(AM4="HCA_Pharm","HCA_Pharm",IF(AN4="HCA_GP","HCA_GP",IF(AO4="Nurse","Nurse",""))))

(I got AM4 and AN4 switched in the original, which sure won't have been helping)

has the same issue, in that it works for the first argument (AL4="HCA"),

but fails for the other three:

I've also tried it with AN4:AO4 returning "" instead of "null" which didn't seem to help. I also had the conditional formatting in AN4:AO4 making the text bold, which I turned off as I read somewhere that can mess with things. 

Thank you for all your help so far, excel is confuse.

[#8 · Accepted solution]

@Tinglytangly 

=IFERROR(VLOOKUP("TZ",$AI$10:$AJ$17,2,FALSE),"Null")

I've used this formula to return Null in all other cells in the sample file.

In your second screenshot the formula should return HCA_Pharm from cell AM4. Can you check if the entry in cell AM4 is "HCA_Pharm" or maybe "HCA_Pharm " with a space in the end?

The nested IF formula returns the intended results in my file. If it still doesn't work for you can you share a sample file without sensitive data or can you share the formulas which return Null in your file?

[#9]

@OliverScheurich That's got it, thank you so much for your help!