Find the first cell in the row containing the number?

Question

I am working in Excel with an exported table, for example:

|-------------------------------------------------------------------------------| | | 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 am trying to compare the current state (last cell) with the original cell (first cell with value).

In the column 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 the number. If there are none in this range, I need G to be "xxx". I suppose I need to check only the first cell of the range (from B2 to F2), which contains a value, not just a number. I tried using a combination of Index and Match, but I couldn't figure it out.

Answer 1

The following formula (in $G$2 and completed) 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 empty cells between the first and last cells.

update: Below is a more universal version from Mr.Excel forum :

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

This should be entered as an "array formula", which means you must press CTRL-SHIFT-ENTER after pasting into the formula.

response to comment:

It works fine, except if all cells are empty in the row, it returns # N / A. So, I slightly modified the formula to include the 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))) 

Instead of duplicating the whole formula, you can 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))) 

Answer 2

The following steps are performed if they are entered as an ARRAY formula (ctrl-shift-enter).

It also works if there are empty cells in the middle.

 {=INDEX(B2:F2,1,MIN(IF(ISBLANK(B2:F2),99999,COLUMN(B2:F2))))} 

Hth

Edit

This one also manages the error:

 {=IF(ISERROR(INDEX(B2:F2,1,MIN(IF(ISBLANK(B2:F2),9999,COLUMN(B2:F2))))),"xxx", INDEX(B2:F2,1,MIN(IF(ISBLANK(B2:F2),9999,COLUMN(B2:F2)))))}