IF Worksheet function

[amitshah]

I am using an IF function in my worksheet to check if the cell contains "#N/A" (this value is got by using the VLOOKUP function).

Using the IF condition as follows:

CellA1 - "Some Text"
CellB1 - "#N/A" (got when vlookup was unsuccessful)
CellC1 - =IF(A1<>"#N/A", A1&" "&B2, A1)

so in this case if cellB1 contains some other text eg. "some other text" then cell C1 would display "Some Text some other text" ie it concatenates text from cell A1 and B1. If it has "#N/A" in cell B1 then cell C1 should display "Some Text".

But in this case it display "#N/A".

Can anyone know why? I am quite sure the IF formula is correct.

 

[Albert 1]

Cell C1 must be:
=IF(B1<>"#NA",A1&" "&B1,A1)
This message was edited by Albert 1 on 2002-04-15 18:59

 

[amitshah]

Thanks for that .....

I am sorry I actually wrote the wrong formula in my question ...... I actually did

=IF(B1<>"#N/A", A1&" "&B1, A1)

but its gives me "#N/A" in C1

 

[anno]

i don't think looking for the text string #N/A will work if the "#N/A" is what a formula is returning. i think this will work though:

=IF(ISNA(B1),A1,A1&" "&B1)
This message was edited by anno on 2002-04-15 19:02
This message was edited by anno on 2002-04-15 19:03

 

[Yogi Anand]

Hi Amit and Albert:
I don't quite know Amit's intent, but the above formula does not work. I believe you want to capture the occurrence of #N/A by using NOT(ISERROR) ...

=IF(NOT(ISERROR(B1)),A1&" "&B2,A1)

Albert: You must be using a European version of Excel -- you are using ; as an argument separator, in North American version we use , as an argument separator.

HTH!

 

[amitshah]

Thanks ...... Yogi ...... thats exactly what I wanted ......

 

[Yogi Anand]

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

Hi anno:
That's short and sweet!

Yogi Anand
This message was edited by Yogi Anand on 2002-04-15 19:09