VLOOKUP & IF or Sumproduct

[Dale G]

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.

 

[Dale G]

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,

 

[RagDyeR]

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!
=====================================================


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,

 

[Dale G]

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","404s","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","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?

 

[Pete_UK]

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

 

[Eduardo]

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(SUMPRODUCT(--(B1=sheet2!a1:a100)),C1="c"),"STD",IF(AND(SUMPRODUCT(--(B1=sheet2!B1:B100)),C1="a"),"GIL",IF(AND(SUMPRODUCT(--(B1=sheet2!B1:b100)),C1="c"),"STA",""))))

 

[Dale G]

Thanks a million, they all work well.
(How do you do that?)

 

[Pete_UK]

Glad to hear that, Dale - thanks for feeding back.

There are often several ways of achieving something in Excel.

Pete