If then formula help

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
:confused:

Hi Folks.

What would a formula be that does the following where there are two columns, A and B, and conditions are only one of those two columns at any given time will have a value, while the other is blank.

A B
__3___ ______
______ __1___
__5___ ______

I want column C to have a formula that says something to the effect of, IF there is a value in column A but none in column B, pull value in Column A, but also the inverse (if there is a value in Column B but none in Column A, pull the only available value from Column B).
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
Awesome! Works.

Is there a way to take it one step further and say all this in one statement?:

IFF D2 contains exactly "ISP & OSP - PRC"
and K2="",L2,K2

Lastly, what formula would give me the following:

If either column H and/or column I have value of zero (meaning either H or I has value of zero or both of them do), then "Eng Not Complete", otherwise "ISP/OSP Complete"
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

=IF(AND(D2="ISP & OSP - PRC",K2=""),L2,K2)

=IF(OR(H2=0,I2=0),"Eng Not Complete","ISP/OSP Complete")
 

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227

ADVERTISEMENT

Awesome. Using that logic I also tried the following but it wasn't working:

If Column J has =IF(OR(H2=0,I2=0),"0","1") and produces a 0
I have Column M off to the side where =IF(AND(J2="1",K2=""),L2,K2)
and even though K2 is not blank, but J2 does not = "1"
the column M formula is still pulling the value from K2 as if it were ignoring the (AND(J2="1" portion of the formula.
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try removing the quotes

=IF(AND(J2=1,K2=""),L2,K2)
 

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227

ADVERTISEMENT

Good thought. But alas it didn't change anything. :(
 

Aimee S.

Board Regular
Joined
Sep 28, 2010
Messages
227
I tried on a whim to make the formula this instead by inversing the K and L:

=IF(AND(J2=1,L2=""),K2,L2)

And it worked! Bizarre...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,369
Messages
5,595,760
Members
414,017
Latest member
surajks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top