• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

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
0
swjtx99
Asked:
swjtx99
3 Solutions
 
FlysterCommented:
Would a nested IF statement work for you:

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

Flyster
0
 
Rgonzo1971Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
If you do not like array formulas then try

=INDEX(A121:C121,LOOKUP(2,1/A121:C121,COLUMN(A121:C121)))
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rory ArchibaldCommented:
If your data are all numeric:

=lookup(1E100,A1:D1)

and copy down.
0
 
swjtx99Author Commented:
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
 
barry houdiniCommented:
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
 
swjtx99Author Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now