Excel – Coalesce Function?

Unfortunately, after googling for a while, I was unable to find a COALESCE function or something similar that natively exists within Excel. Therefore, I decided to write a quick-and-dirty formula that would approximate it. I used the following array formula to pull the first non-blank value from cells C2 to C10 in a worksheet:
=INDEX($C$2:$C$10,MATCH(FALSE,ISBLANK($C$2:$C$10),FALSE))

As you can see, I used three Excel functions get my desired result. It would be nice to have less, but you can’t expect everything from this poor man’s database. 😀 Anyway, the ISBLANK function does exactly what it sounds like it does: it indicates whether or not the specified cell is blank. Since this is an array formula, ISBLANK($C$2:$C10) evaluates to an array of 9 TRUE/FALSE values. The MATCH function searches for the first parameter in the specified array (the second parameter) and evaluates to the position of the first match. The reason I supplied FALSE as a third parameter was to make sure that if every value was blank, a #N/A error would be returned. The INDEX function takes an array as the first parameter, and the position in the array to evaluate to as the second parameter.

In order to make the the formula evaluate as an array formula, you must use the CTRL + ENTER key combination to instead of simply pressing ENTER after inputting the formula. If you are using a Mac, the key combination is COMMAND + ENTER.

In conclusion, this is not the optimal solution, but it works and it doesn’t require a UDF (AKA macro).