2 cells 1 with NA and one with a value

[mecheet]

I have 2 columns, which have alternate NAs and values.

I need a formula to show the value of either cell in the column.

e.g. in A1 its NA and B1 its Yes
in A2 its and No and in B2 its NA

I need cell C1 to show yes and C2 to show now.

what formula can be used to show this?

 

[Sektor]

Try this.

=IF(OR(A1="yes",B1="yes"),"yes","no")

 

[jasonb75]

Maybe

=IF(ISNA(A1),B1,A1)

 

[mecheet]

sorry wasnt very clear with my example, there are quite a lot of options that the value can be in either column A or B.

but if there is NA in the row i want C to show the value from either A or B

this is specific to yes and no, which i cant be so specfic about due to the many posibly values

 

[Peter_SSs]

Similar to Jason's - if you have Excel 2007+ (and assuming the NA's are actually error values)

=IFERROR(A1,B1)


If the 'NA's are text then

=IF(A1="NA",B1,A1)

 

[mecheet]

i have now realised that some of the data in columns A and B have #VALUE!

is there anyway to incorporate this into the formula.

=IF(ISNA(A1),B1,A1)

 

[jasonb75]

If you have excel 2007 or newer try Peter's suggestion of =IFERROR(A1,B1)

otherwise =IF(ISERROR(A1),B1,A1)

 

[Peter_SSs]

Also, if you do not have Excel 2007+, if the non-error values are text, then you could try

=LOOKUP(REPT("z",255),A1:B1)

 

[mecheet]

thanks for your help with this, ive now realised that i need a 3rd column.

How could i do this?

the current formula that im using is

=IF(ISERROR(A2),B2,A2)

i now need to check a 3rd source, but it appears the above is just for 2

 

[Peter_SSs]

If 2 out of 3 cells are errors, then use my formula from post #8 but just make the range at the end 3 cells instead of 2

=LOOKUP(REPT("z",255),A1:C1)

If that does not cover what you have, give some sample data and expected results.