0

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?

Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • Can you add other columns to the table? You could use `ISNA()` and look for `True`/`False` values. – Vinnie May 10 '17 at 10:25
  • @Vinnie it would be better to don't add column (as I'll have more than 180 columns) but if there is not another way it is OK to add them. – Nicolaesse May 10 '17 at 11:55

2 Answers2

1

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

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

enter image description here

Vinnie
  • 553
  • 1
  • 6
  • 10
0

Are you not putting the #N/D text within quotation marks when using the MATCH function coupled with the INDEX function: =INDEX(Month,MATCH("#N/D",Price alpha,0),1)? I assume that you have the columns named by the headers.

Roger H.
  • 326
  • 3
  • 7
  • The problem is not about the quotation mark. If I write "#N/D" the software only consider the cell where there is "#N/D" as a string of test and not as a result of a formula with error. – Nicolaesse May 10 '17 at 12:28