Hi Dale,
It will be better to keep the codes in a different sheet, so if you add new
ones the formula will pick up
let's say you enter codes in sheet2 in column A the 400s and in column B the
500s

then in your sheet place the formula as follow

=IF(AND(SUMPRODUCT(--(B1=sheet2!a1:a100)),C1="a"),"CTA",IF(AND(SUMPRODU CT(--(B1=sheet2!a1:a100)),C1="c"),"STD",IF(AND(SUMPRODU CT(--(B1=sheet2!B1:B100)),C1="a"),"GIL",IF(AND(SUMPRODU CT(--(B1=sheet2!B1:b100)),C1="c"),"STA",""))))

"Dale G" wrote:

Yes sir that works well, thank you.

Here is the result, and how I will need to use it.

=IF(OR(K3={"401n","401s","402n","402s","404n","404 s","405n","405s","406n","406s","408n","408s","410n ","410s","411n","411s","413n","414n","414s","415n" ,"415s","416n","416s","417n","417s","421n","421s", "422n","422s","425n","425s","441n","441s","477n"," 477s"}),IF(M3="a","CTA",IF(M3="c","STD","")),IF(OR (K3={"510n","510s","511n","511s","513n","513s","53 2n","532s","535n","535s"}),IF(M3="a","GIL",IF(M3=" c","STA",""))))

As you can see I have more number text combos

Do you think I would do better by creating a LOOKUP table on a separate sheet?

If so how could I apply the LOOKUP to return the correct data?


"RagDyeR" wrote:

Try this in A1:

=IF(OR(B1={"401n","401s","402n","402s"}),IF(C1="a" ,"CTA",IF(C1="c","STD","")),
IF(OR(B1={"501n","501s","502n","502s"}),IF(C1="a", "GIL",IF(C1="c","STA",""))))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Dale G" wrote in message
...
Correction to the last IF. the number text combo start with a 5.

My dilemma today is, how to have Cell A1 fill automatically with CTA, or
STD,
or GIL, or STA when the following conditions are true.
if Cell C1 is "a" & Cell B1 is "401n", or "401s", or "402n", or "402s"
then
Cell A1 will = CTA
if Cell C1 is "c" & Cell B1 is "401n", or "401s", or "402n", or "402s"
then
Cell A1 will = STD
if Cell C1 is "a"& Cell B1 is "501n", or "501s", or "502n", or "502s" then
Cell A1 will = GIL
if Cell C1 is "c" & Cell B1 is "501n", or "501s", or "502n", or "502s"
then
Cell A1 will = STA
The number text combination ("401n") is in column B before I receive the
file. I would like to enter the "a" or the "c" in column C and have the
proper data auto enter in Column A.


"Dale G" wrote:

My dilemma today is, how to have Cell A1 fill automatically with CTA, or
STD,
or GIL, or STA when the following conditions are true.
if Cell C1 is "a" & Cell B1 is "401n", or "401s", or "402n", or "402s"
then
Cell A1 will = CTA
if Cell C1 is "c" & Cell B1 is "401n", or "401s", or "402n", or "402s"
then
Cell A1 will = STD
if Cell C1 is "a"& Cell B1 is "501n", or "501s", or "502n", or "502s" then
Cell A1 will = GIL
if Cell C1 is "c" & Cell B1 is "401n", or "401s", or "402n", or "402s"
then
Cell A1 will = STA
The number text combination ("401n") is in column B before I receive the
file. I would like to enter the "a" or the "c" in column C and have the
proper data auto enter in Column A.