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(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 😞
