Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: IF Worksheet function

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Cell C1 must be:
    =IF(B1<>"#NA",A1&" "&B1,A1)


    [ This Message was edited by: Albert 1 on 2002-04-15 18:59 ]

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •