Forum Discussion
Returning a result based on 1 of 4 other cells
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:
=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?
8 Replies
=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)
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.
- 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 😞
=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.
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...
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”;””))))
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";""))))
