Solved

# Excel =If(ISBLANK) with 4 columns

Posted on 2013-11-12
Medium Priority
272 Views
Hi,

I have an existing formula in C that puts the number in Cell A into Cell C if B is blank. otherwise it puts B into C

What I need is a formula in Column D that puts A into D if B and C are blank, or puts B into D if C is blank, otherwise, it puts C into D.

In other words here is what should be in D for each row:

A    B      C      D
5                     5
5    6               6
4               4
3       3
2   3       1       1

It was easy with only 3 columns using =IF(ISBLANK(B121),A121,B121) but I haven't found an example using 4 columns.

swjtx99
0
Question by:swjtx99

LVL 22

Assisted Solution

Flyster earned 668 total points
ID: 39643630
Would a nested IF statement work for you:

=IF(B121&C121="",A121,IF(C121="",B121,C121))

Flyster
0

LVL 54

Expert Comment

ID: 39643755
Hi,

With this formula you can have as many columns as you want
``````=INDEX(A2:C2,1,MAX((A2:C2<>"")*(COLUMN(A2:C2))))
``````

Enter as Array Formula Ctrl-Shift-Enter

Regards
0

LVL 43

Expert Comment

ID: 39643822
If you do not like array formulas then try

=INDEX(A121:C121,LOOKUP(2,1/A121:C121,COLUMN(A121:C121)))
0

LVL 85

Assisted Solution

Rory Archibald earned 664 total points
ID: 39644169
If your data are all numeric:

=lookup(1E100,A1:D1)

and copy down.
0

Author Comment

ID: 39644766
Thanks for all the suggestions.

=IF(B121&C121="",A121,IF(C121="",B121,C121))

From Flyster - This one works.

=INDEX(A2:C2,1,MAX((A2:C2<>"")*(COLUMN(A2:C2))))

From Rgonzo Thanks but I can't use an array on this sheet

=INDEX(A121:C121,LOOKUP(2,1/A121:C121,COLUMN(A121:C121)))

From ssabqih works but when I start inserting columns and the column with the formula shifts (say from D to E) the formula breaks. Looks like it has something to do with the "LOOKUP(2,1/" part ?

=lookup(1E100,A1:D1)

From rorya. When I used it, it kept changing to =lookup(1E+100,A1:D1 and appeared to be summing all rows.
0

LVL 50

Accepted Solution

barry houdini earned 668 total points
ID: 39644812
Rory's formula works for me. If you put the formula in E1

=LOOKUP(1E+100,A1:D1)

it will give you the last numeric value in A1:D1....which seems to be what you want

regards, barry
0

Author Closing Comment

ID: 39653209
Hi,

I did also find that Rory's formula works. As both Rory's and Flyster's seem to work equally well I have split the points.

Thanks very much for the assistance.

swjtx99
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.