How to Coalesce Excel Data

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 of the arguments are blank or empty strings then the function returns blank, making Coalesce a good way to convert empty strings to blank values. All arguments to Coalesce must be of the same type; for example, you can’t mix numbers with text strings.

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.

-------------------- 
|Book | Page|Index | 
--------------------
|  100 | 1  |  AI   |    
|  100 | 2  |  ML   |   
|  100 | 7  |  ML   |   
|  101 | 4  |  AI   |   
|  101 | 9  |  ML   |  
|  103 | 6  |  ML   |  
-------------------- 

Changing the data format from the above table

------------------ 
Page|AI|ML
-------------------  
| 1 | 100    |NULL |
| 2 | NULL|100     |
| 7 | NULL|100     |
| 4 | 101    |NULL |
| 9 | NULL|101     |
| 6 | NULL|103     |
-------------------- ------

But, what I am looking the data to be look like is as follow:

-------------------------- 
BookID | Page |AI |ML
--------------------------
| 100 | 1 | Yes |No|
| 100 | 2 | No|Yes|
| 100 | 7 | No|Yes|
| 101 | 4 | Yes|NULL |
| 101 | 9 | No|Yes|
| 103 | 6 | No|Yes|
-------------------------- 

In order to achieve the above table result, note the derived column BookID, I want to coalesce the AI and ML fields if they’re NULL or contains the BookID. Thus, the following work around the excel functions will return the desired result.

{=INDEX(C2:D2,MATCH(FALSE,ISBLANK(C2:D2),FALSE))}

Don’t forget to enter this in the formula bar and press