The COALESCE function is used to return the first non-null value in a list of arguments. It's quite handy when you have multiple columns of data and you want to find the first non-empty value among them.
Google Sheets currently does not have a built-in COALESCE function. However, you can achieve similar functionality using the IF or IFS functions combined with the ISBLANK function in Google Sheets. Here are some simplified examples:
If you have a limited number of values to check, you can use a nested IF function combined with ISBLANK. For example, if you have three cells (A1, B1, and C1) and you want to return the first non-blank value, you can use the following formula:
=IF(ISBLANK(A1), IF(ISBLANK(B1), C1, B1), A1)
This formula checks if A1 is blank. If it is, it then checks B1. If B1 is also blank, it returns C1. Otherwise, it returns B1. If A1 is not blank, it returns A1.
For a slightly cleaner approach with multiple conditions, you can use the IFS function:
=IFS(NOT(ISBLANK(A1)), A1, NOT(ISBLANK(B1)), B1, TRUE, C1)
The IFERROR function in Google Sheets can be used to return a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula. Here's how you might use it:
=IFERROR(A1, IFERROR(B1, "No numeric value"))
If there is no numeric value:
We hope that this article has helped you and given you a better understanding of how to coalesce in Google Sheets. If you enjoyed this article, you might also like our articles on how to use FILTER with a wildcard in Google Sheets and how to set up a GPA calculator in Google Sheets.
