Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If / Then Formula (97)

    I want to write a IF/Then Formula
    If B20=073, 302 / Then C20=SW
    If B20=231, 244 / Then C20=WC
    If B20=171, 176, 043 / Then C20=SE
    If B20=332, 310 / Then C20=MW
    If B20=Any other number, / Then C20=Blank Cell

    Is there a way to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If / Then Formula (97)

    A flexible way is to create a small table with the corresponding values. Say that A1:A9 contains the numbers 043, ..., 332, and B1:B9 the corresponding strings. The formula inC20 would be

    =IF(ISNA(VLOOKUP(B20,$A$1:$B$9,2,FALSE)),"",VLOOKU P(B20,$A$1:$B$9,2,FALSE))

    The VLOOKUP is the main part: VLOOKUP(B20,$A$1:$B$9,2,FALSE) looks up the value of B20 in the first column of the table A1:B9 ands returns the corresponding value from the second column.
    The ISNA part replaces the "not found" error (#N/A) with a blank.

    See attached workbook.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    Does "If B20=073, 302" mean if B20 equals seventy three thousand three hundred two, or does it mean if B20 equals 073 or if B20 equals 302? Also, does the leading zero on 073 indicate that B20 is a text value, or is it numeric?
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    If B20=073 or 302 Then C20=SW

  5. #5
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    Hans,
    Great Formula except that if it does not equal A1:B9 then C20=######################
    Is there a way to make C20 a blank cell?
    Lania

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If / Then Formula (97)

    It doesn't do so in the workbook I attached - that's what the IF(ISNA(... part is for.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    If that is what you want, and B20 is a numeric value not a text value, then Hans formula looks to me like it will give you exactly what you asked for. I do not understand your response to Hans. What do you mean by "except that if it does not equal A1:B9 then C20=######################"? And what do you mean by "Is there a way to make C20 a blank cell?" I thought you said "If B20=073 or 302 Then C20=SW." It that is true, then C20 can't be blank.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    Hans,
    Thanks for being so patient. Please take a look at this spreadsheet and tell me if I am doing anything wrong.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If / Then Formula (97)

    It probably has to do with the formatting. If I select a cell in column B and look at the number formatting, it displays Chinese characters. You can get around it as follows:
    - Select column C and set the number format to General instead of Text.
    - Replace ISNA in the formula with ISERROR

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    Is this what you want?
    Legare Coleman

  11. #11
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If / Then Formula (97)

    Legare and Hans,
    Thanks so much for both of your suggestions and help. It is now working beautifully by changing ISNA to ISERROR.
    Lania

Posting Permissions

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