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