|
VLOOKUP & IF or Sumproduct
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. |
VLOOKUP & IF or Sumproduct
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. |
VLOOKUP & IF or Sumproduct
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. |
VLOOKUP & IF or Sumproduct
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. |
VLOOKUP & IF or Sumproduct
Using your first set of data as an example (because it is shorter),
you need to build up a table of two columns in Sheet2 showing the combinations and the desired outcome. Assume it occupies A1 to B16 like this: a401n CTA a401s CTA a402n CTA a402s CTA c401n STD c401s STD c402n STD c402s STD a501n GIL a501s GIL a502n GIL a502s GIL c501n STA c501s STA c502n STA c502s STA Note that I have combined the letter with the alpha numeric code in the first column. Then, with your code in B1 of sheet1, and C1 containing either "a" or "c" (without the quotes), you can put this formula in A1: =IF(ISNA(MATCH(C1&B1,Sheet2!A$1:A$16,0)),"not valid",VLOOKUP (C1&B1,Sheet2!A$1:B$16,2,0)) You can change "not valid" to whatever message you prefer (or just ""). Hope ths helps. Pete On Aug 19, 5:14*pm, 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","410 n","410s","411n","411s","413n","414n","414s","415n "*,"415s","416n","416s","417n","417s","421n","421s ","422n","422s","425n","425*s","441n","441s","477n ","477s"}),IF(M3="a","CTA",IF(M3="c","STD","")),IF (OR*(K3={"510n","510s","511n","511s","513n","513s" ,"532n","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.- Hide quoted text - - Show quoted text - |
VLOOKUP & IF or Sumproduct
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. |
VLOOKUP & IF or Sumproduct
Thanks a million, they all work well.
(How do you do that?) "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. |
VLOOKUP & IF or Sumproduct
Glad to hear that, Dale - thanks for feeding back.
There are often several ways of achieving something in Excel. Pete On Aug 19, 8:35*pm, Dale G wrote: Thanks a million, they all work well. * (How do you do that?) |
| All times are GMT +1. The time now is 02:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com