Find first cell in a row that contains a number?

Question

I'm working in Excel with an exported table such as this:

|-------------------------------------------------------------------------------|
|   |        A          |  B  |  C  |  D  |  E  |  F  |   G   |  H  |     I     |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 1 |      Domain       | JAN | FEB | MAR | APR | MAY | Start | End |   Change  |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 2 | www.mydomain1.com |     |   1 |   4 |   3 |   1 |     1 |   1 |         0 |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 3 | www.mydomain2.com |   2 |   4 |  12 |  18 |  23 |     2 |  23 |        21 |
|---|-------------------|-----|-----|-----|-----|-----|-------|-----|-----------|
| 4 | www.mydomain3.com |     |     |  14 |  12 |     |    14 | xxx | NOT FOUND |
|-------------------------------------------------------------------------------|

I'm trying to compare the current state (last cell) to the original cell (first cell with a value).

In column I, I have the formula =IF(G2 = "xxx", "NOT FOUND", IF(H2 = "xxx", "NOT FOUND", H2 - G2))

In column H, I have the formula =IF(F2 = "", "xxx", F2)

In column G, I need to find the first cell with a number. If there isn't one in that range, I need G to be "xxx". I suppose I only need to check for the first cell in the range (B2 to F2) that contains a value, not just a number. I tried using an Index and Match combo, but I couldn't quite understand it.

e.James e.James · Accepted Answer · 2010-12-22T17:39:56

The following formula (in $G$2 and filled down) should do the trick:

=IF(ISBLANK(F2),"xxx",INDEX(B2:E2,,COUNTBLANK(B2:F2)+1))

Note that this will only work if there are no other blank cells in between the first and last entries.

update: Here is a more versatile option from the Mr.Excel forum:

=INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0))

This one has to be entered as an "array formula" which means you must press CTRL-SHIFT-ENTER after pasting in the formula.

response to comment:

It works great, except that if all cells are empty on a row, it returns #N/A. So, I changed the formula slightly to include an IsNA() condition:

=IF(ISNA(INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0))), "xxx",
    INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0)))

Rather than duplicate the whole formula, you could do the following:

=IF(COUNTBLANK(B2:F2)=COLUMNS(B2:F2),"xxx",
    INDEX(B2:F2,MATCH(1,IF(B2:F2<>0,IF(B2:F2<>"",1)),0)))

Find first cell in a row that contains a number?

2 Answers