asked on

Excel =If(ISBLANK) with 4 columns

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.

Thanks in advance,

swjtx99

SOLUTION

Flyster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

Hi,

With this formula you can have as many columns as you want

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

Open in new window

Enter as Array Formula Ctrl-Shift-Enter

Regards

Saqib Husain

If you do not like array formulas then try

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

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swjtx99

ASKER

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.

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swjtx99

ASKER

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