IF FORMULA

[Karen Smith]

I need a formula for the following:

If D6=STG, then place "IMC COE" in cell D7
If D6=SWG, then place "SWG IMC" in cell D7
If D6=S&D, then place "S&D IMC" in cell D7
If D6=IGF, then place "IGF IMC" in cell D7
If D6=GTS, then place "GTS IMC" in cell D7
If D6=GBS, then place "GBS IMC" in cell D7
If D6=Alliances, then place "Alliances IMC" in cell D7

 

[sacrame]

=IF(B19="STG","IMC COE",IF(B19="SWG","SWG IMC",IF(B19="S&D","S&D
IMC",IF(B19="IGF","IGF IMC",IF(B19="GTS","GTS IMC",IF(B19="GBS","GBS
IMC",IF(B19="Alliances","Alliances IMC" )))))))

Here B19=D6 and type the above in D7. However we have a limitation of using
only 7 ifs.

 

[jjones]

Hi Karen,

You could write a really complex IF statement (Excel limits you to 7 nested
layers), OR, (and this is the way I would do it) you could create a table
with all possible values listed and then use a VLOOKUP formula to pull in
what you're looking for.

Start by creating your table. It can be on the same sheet or in a separate
sheet. Let's assume you will put it on a separate sheet. I will assume
Sheet2. Your table should look something like this:

Column A Column B
STG IMC COE
SWG SWG IMC
S&D S&D IMC
IGF IGF IMC
GTS GTS IMC
GBS GBS IMC
Alliances Alliances IMC

Then back on your other sheet, enter the following formula in cell D7:

=VLOOKUP(D6,Sheet2!A:B,2,FALSE)

If you put your table on something other than Sheet2, then just make sure
your change the formula to match.

 

[Stephen]

The best approach would be to list the possibilities for D6 (STG, SWG, etc)
down one column somewhere and the required results for D7 down the next
column. (For example, this could be in X101:Y107, or anywhere you choose,
even on another sheet.) Then in D7 you would use the formula:
=VLOOKUP(D6,X101:Y107,2,FALSE)
This way, it's easy to check and modify/extend. (A multiple IF statement
would be limited to 7 nestings, and difficult to read.)

 

[Sandy Mann]

Try:

=IF(D6="STG","IMC
COE",IF(OR(D6="SWG",D6="S&D",D6="IGF",D6="GTS",D6="GBS",D6="Alliance"),D6&"
IMC",""))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

Look in the help index for CHOOSE or LOOKUP

 

[RagDyeR]

If you don't wish to create a datalist, here's one way:

=LOOKUP(D6,{"STG","SWG","S&D","IGF","GTS","GBS","Alliances";"IMC COE","SWG
IMC","S&D IMC","IGF IMC","GTS IMC","GBS IMC","Alliances IMC"})

Entered in D7.
--

HTH,

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


I need a formula for the following:

If D6=STG, then place "IMC COE" in cell D7
If D6=SWG, then place "SWG IMC" in cell D7
If D6=S&D, then place "S&D IMC" in cell D7
If D6=IGF, then place "IGF IMC" in cell D7
If D6=GTS, then place "GTS IMC" in cell D7
If D6=GBS, then place "GBS IMC" in cell D7
If D6=Alliances, then place "Alliances IMC" in cell D7

 

[Don Guillett]

Or
=IF(D6="stg","IMC COE",D6&" IMC")

 

[RagDyeR]

Nice !

And that's not even considering I forgot to sort my list.<bg>

 

[Ed Cones]

Please note that the values in LOOKUP() much be placed in ascending order for
it to work as expected.

 

[RagDyer]

I know.<bg>

Didn't you see the mention of my "faux pas" in my post to Don?

 

[Ed Cones]

No. Probably because I was still trying to figure out why the heck it
wouldn't work when you posted it. ;o)

 

[RagDyeR]

You're right ... I should have corrected it as soon as I realized my
mistake.

However, Don's solution was so vividly concise, and posted above mine, I
figured anyone viewing this thread would choose to test and/or use his right
off.

 

[Ed Cones]

My post was not intended to chastise. I was laughing at myself, which is
very easy to do. You have a terrific Christmas.

 

[RagDyer]

And a Very Merry Christmas to you too!