MATCH function with #N/D cells

Question

I have the following table in Excel:

+----+--------+-------------+------------+-------------+
|    |   A    |      B      |     C      |      D      |
+----+--------+-------------+------------+-------------+
|  1 | Month  | Price alpha | Price Beta | Price Gamma |
|  2 | 201601 |             | #DIV/0!    |             |
|  3 | 201602 | 51          | 21         | 93          |
|  4 | 201603 | 47          | 22         | 97          |
|  5 | 201604 | 44          | 28         | 92          |
|  6 | 201605 | 58          | 44         | 98          |
|  7 | 201606 | #N/D        | 28         | 35          |
|  8 | 201607 | #N/D        | 44         | #N/D        |
|  9 | 201608 | #N/D        | #N/D       | #N/D        |
| 10 | 201609 | #N/D        | #N/D       | #N/D        |
| 11 | 201610 | #N/D        | #N/D       | #N/D        |
| 12 | 201611 | #N/D        | #N/D       | #N/D        |
| 13 | 201612 | #N/D        | #N/D       | #N/D        |
+----+--------+-------------+------------+-------------+

For each column there is a variable list of numerc values (and, maybe, few #DIV/0! errors) and, from a specific rows to the end of the table, only #N/D values.

My goal is to have, for each column, then first Month where the #N/D values start. The results would be:

• Price alpha: 201606
• Price Beta: 201608
• Price gamma: 201607

For this king of tasks I usually write a function cobining MATCH and INDEX but, unfortunally, the MATCH function doesn't accept #N/D as value to look for in the matrix.

How could I get the first #N/D error for each column?

Answer 1

You have to use an array function (Ctrl+Shift+Enter).

{=MATCH(TRUE,ISNA(A:A),0)}