MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF function


Posted by Dianne on December 16, 2000 6:42 AM

I am trying to write an IF as follows:

I need cell C4 to change to a 20 if cell b4 has an A, to 20.75 if B4 has a B, 13 if B4 has a C and so forth. I have about 20 different letters that need to change C4 to a certain number. I have tried it every way I know, and keep coming up with VALUE, so I am doing something wrong.

Thanks in advance for your help. Greatly appreciate it.


Posted by Bruce on December 16, 2000 8:02 AM

Try this:
=IF(B4="a",20.75,IF(B4="b",13,IF(B4="c",3,"Not Found")))

The last one "Not found" tells you that you have a value that does
not match.

But, becaue you have so many values, you should think about using
a vlookup function.

Posted by Aladin Akyurek on December 16, 2000 8:07 AM

Make a 2-column table that contains your 20 letters in the first column and the corresponding values in the second column. Select these values and name it MyTable (or something meaningful) on some sheet. Then use the following formula in C4

=IF(ISNA(VLOOKUP(B4,MyTable,2,FALSE)),"NoValue",VLOOKUP(B4,MyTable,2,FALSE)

You can replace "NoValue" by "" or something else that is useful.

Aladin

Posted by Dianne on December 16, 2000 8:49 AM

Thank you so much!! It is working.
Don't know what I would do without this
group.
Thanks again.
Dianne