Results 1 to 11 of 11
Thread: If / Then Formula (97)

20050803, 18:44 #1
 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?

20050803, 18:55 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050803, 19:44 #3
 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

20050803, 20:05 #4
 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

20050803, 20:08 #5
 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

20050803, 20:32 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20050803, 20:33 #7
 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

20050803, 20:48 #8
 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.

20050803, 20:56 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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

20050803, 21:01 #10
 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

20050804, 11:40 #11
 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