I want excel to input data in a cell from multiple cell choices

[Pam]

In cell Q11.

I want it to display the number 2, if Cell D11 says ICW, I want it to
display the number 1, if F11 says ICW, I want it to display the number 3, if
L11 says ICW, and finally, I want it to display the number 5, if cell E11
says ASFC.

If none of these apply, I want it to say NA.

I have tried dozens of formulas and I might get the values I want, but I get
a blank instead of NA

 

[Gary''s Student]

=IF(D11="ICW",2,IF(F11="ICW",1,IF(L11="ICV",3,IF(E11="ASFC",5,"N/A"))))

 

[Pam]

I pasted your formula into cell Q11 and it keeps giving me a number 2, no
matter what entry I make.

 

[Gary''s Student]

If the formula returns 2, then D11 must be "ICW"

If D11 has the proper value, then all the other conditions are ignored.

 

[Gord Dibben]

As long as you have ICW in D11 it won't matter what you have in the other
cells, you will return 2

You will get the correct results if only one of the cells has the correct
entry or none of the cells has the correct entry........then you get N/A


Gord Dibben MS Excel MVP

 

[Pam]

Ok, the formula works in a brand new blank worksheet, but not in the one I
have all of my data in. What am I doing wrong?

 

[Gord Dibben]

In your current workbook with the data what are the values of D11, E11, F11
and L11


Gord

 

[Pam]

It changes daily. Sometimes there are various numbers throughout the row,
from D11 to O11. I only need to track and change the value if the ICW or
ASFC land in one of those columns. There are on most occasions multiple
numbers in a row, hence the NA.

For Example

D11 E11 F11 G11 H11 I11 J11 K11 L11 M11 N11 O11
2 8 4
NA
OR

ICW 3
2