Column of Data - Set Next Column Value Conditionally

[Cecil]

Column A contains text data ... unlimited set of values. Column B
contains a limited set of values, depending on the value of column A.
The Column B values are pre-set by another program. I would like to
_change_ the value of a column B cell if the column A value equals a
certain value. Otherwise the column B value should stay as is. How
can I do that?

Example Data Set
COL-A COL-B
AAA horse
BBB cow
CCC donkey
DDD dog
BBB cow
MMM cat
Situation > If COL-A value = DDD, would like to change value in COL-B
from donkey to mule

 

[Bernard Liengme]

You cannot do this with a function since a cell cannot have BOTH its own
value and a formula that changes it.
You could use a 'helper' column. In C1 for example, we could have
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B100,2,FALSE),B1,
VLOOKUP(A1,Sheet2!A1:B100,2,FALSE))
The formula looks at A1,attempts to locate its value in a table on Sheet2;
if it is not found then C1 gets B1's value otherwise a value from the table
is used.

I suppose you could also hid column B.
best wishes

 

[Gary]

Enter this in B1 and drag it down.

=If(A1="DDD","mule","donkey")