Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > VLOOKUP & IF or Sumproduct
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 85
Default 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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 85
Default 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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,572
Default 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.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 85
Default 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.




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,856
Default 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 -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,276
Default 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.




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 85
Default 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.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,856
Default 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?)

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct or VlookUp Venice Excel Discussion (Misc queries) 1 July 2nd 09 06:44 AM
Sumproduct and Vlookup Richard Excel Worksheet Functions 3 February 18th 09 07:19 PM
VLOOKUP, IF, SUMPRODUCT...oh my! Greg in CO[_2_] Excel Worksheet Functions 8 August 20th 08 07:22 PM
VLOOKUP? SUMPRODUCT? not sure how to do this Gambit-6 Excel Worksheet Functions 4 July 22nd 08 02:53 PM
sumproduct and vlookup Tami Excel Worksheet Functions 2 June 3rd 08 02:40 PM


All times are GMT +1. The time now is 03:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement