# Thread: If / Then Formula (97)

1. ## 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. ## 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. ## 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?

4. ## Re: If / Then Formula (97)

If B20=073 or 302 Then C20=SW

5. ## 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. ## 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. ## 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.

8. ## 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. ## 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. ## Re: If / Then Formula (97)

Is this what you want?

11. ## 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
•