User Profile
Tinglytangly
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
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:Re: Returning a result based on 1 of 4 other cells
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.
