How do I combine IF and OR?

[ana_15825 - ExcelForums.com]

I am very new to Excel (I've only used it a few times before this). I
have already figured out (mostly by accident) how to get my worksheet
to give me the following:

One cell is for input of the company Division numbers. When a number
is entered, the employee number and manager name of that Division
appear in other cells. If nothing is entered, I want it to remain
blank.

My current formula for six of the employee numbers and the "blank"
is:

=IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701,"40323",IF(K53=801,"40323",IF(K53=601,"99253",
IF(K53=401,"41723",IF(K53=""," ")))))))

Since I can only nest seven things, I can't get the seventh or eighth
manager into the formula and still keep the blank option. However,
currently three of the managers are each overseeing two or three
Divisions each, so I'd like to add an OR statement in there. I'd like
it to say that if K53 has either 301 or 901 to put employee 11517, if
K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685
to put employee 49325, if K53 is 662 to put employee 05498, and keep
the rest of the IF statements as they are. Division 662 is a special
exceptions thing, though, so rarely used and can be left out if need
be.

I don't know how to set up the formula though. I tried doing just one
of the managers and thought I had it, but keep getting "FALSE" when I
test it. Can any more experienced person help me get a formula that
works (with or without the 662 guy).

Thanks.

[Aladin Akyurek]

Try to use the VLOOKUP function instead of a bunch of IF's.

ana_15825 - ExcelForums.com wrote:
> I am very new to Excel (I've only used it a few times before this). I
> have already figured out (mostly by accident) how to get my worksheet
> to give me the following:
>
> One cell is for input of the company Division numbers. When a number
> is entered, the employee number and manager name of that Division
> appear in other cells. If nothing is entered, I want it to remain
> blank.
>
> My current formula for six of the employee numbers and the "blank"
> is:
>
> =IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701,"40323",IF(K53=801,"40323",IF(K53=601,"99253",
> IF(K53=401,"41723",IF(K53=""," ")))))))
>
> Since I can only nest seven things, I can't get the seventh or eighth
> manager into the formula and still keep the blank option. However,
> currently three of the managers are each overseeing two or three
> Divisions each, so I'd like to add an OR statement in there. I'd like
> it to say that if K53 has either 301 or 901 to put employee 11517, if
> K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685
> to put employee 49325, if K53 is 662 to put employee 05498, and keep
> the rest of the IF statements as they are. Division 662 is a special
> exceptions thing, though, so rarely used and can be left out if need
> be.
>
> I don't know how to set up the formula though. I tried doing just one
> of the managers and thought I had it, but keep getting "FALSE" when I
> test it. Can any more experienced person help me get a formula that
> works (with or without the 662 guy).
>
> Thanks.
>

[ana_15825 - ExcelForums.com]

VLOOKUP won't work. It's a form, not a table, K53 is not nearby.
Thanks for answering.

[CLR]

The OR statement can be combined with the IF statement as
follows.........adjust to suit.........

=IF(OR(K53=301,K53=901),"11517",IF(OR(K53=701,K53=801),"40323", etc
etc.......

Vaya con Dios,
Chuck, CABGx3




"ana_15825 - ExcelForums.com" <ana_15825@hotmail-dot-com.no-spam.invalid>
wrote in message news:OmKagePfFHA.2384@TK2MSFTNGP15.phx.gbl...
> I am very new to Excel (I've only used it a few times before this). I
> have already figured out (mostly by accident) how to get my worksheet
> to give me the following:
>
> One cell is for input of the company Division numbers. When a number
> is entered, the employee number and manager name of that Division
> appear in other cells. If nothing is entered, I want it to remain
> blank.
>
> My current formula for six of the employee numbers and the "blank"
> is:
>
>
=IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701,"40323",IF(K53=801,"40323"
,IF(K53=601,"99253",
> IF(K53=401,"41723",IF(K53=""," ")))))))
>
> Since I can only nest seven things, I can't get the seventh or eighth
> manager into the formula and still keep the blank option. However,
> currently three of the managers are each overseeing two or three
> Divisions each, so I'd like to add an OR statement in there. I'd like
> it to say that if K53 has either 301 or 901 to put employee 11517, if
> K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685
> to put employee 49325, if K53 is 662 to put employee 05498, and keep
> the rest of the IF statements as they are. Division 662 is a special
> exceptions thing, though, so rarely used and can be left out if need
> be.
>
> I don't know how to set up the formula though. I tried doing just one
> of the managers and thought I had it, but keep getting "FALSE" when I
> test it. Can any more experienced person help me get a formula that
> works (with or without the 662 guy).
>
> Thanks.
>

[Max]

"ana_15825 - ExcelForums.com" wrote
> VLOOKUP won't work. It's a form, not a table, K53 is not nearby

But I seem to be able to get what Aladin suggested to work? <g>

In Sheet2, the list below was created in A1:B10
(Numbers in col B were entered as text with a leading apostrophe, following
your specimen data in the orig. post)

301 11517
901 11517
701 40323
801 40323
601 99253
401 41723
501 49325
503 49325
685 49325
662 05498
etc

(Note that col A above need not be sorted as we're going for an exact match
in the VLOOKUP)

In Sheet1,

if we put in say, B2 (can be any cell other than K53):
=IF(K53="","",VLOOKUP(K53,Sheet2!A:B,2,0))

B2 seems to return the correct corresponding text number from col B in
Sheet2 for the input in K53. And if K53 is cleared, a blank: "" will be
returned (Think returning a blank: "" is better than returning a space: " ",
to achieve the same visual effect). If there's no match for the input in
K53, you'll get an #N/A error. If we wanted a blank: "" to be returned for
non-matching inputs in K53 (which could also cover the instance of K53 being
cleared),
we could put instead in B2:

=IF(ISNA(VLOOKUP(K53,Sheet2!A:B,2,0)),"",VLOOKUP(K53,Sheet2!A:B,2,0))

IMO, the suggested VLOOKUP approach does seem a neater way to do it (we
could simply extend the reference table in Sheet2 to suit future additions,
for example, w/o having to change the formula)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Max]

Here's a sample file with the implemented construct:
http://www.savefile.com/files/9843022
File: ana_15825_wksht.xls

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Ragdyer]

Can even be a little shorter if you use array constants:

=IF(K53="","",IF(OR(K53={301,901}),11517,IF(OR(K53={701,801}),40323,IF(K53=6
01,99253,IF(K53=401,41723)))))

I left the employee numbers as numbers by eliminating the quotes.
If necessary, you can add them back.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"CLR" <croberts@tampabay.rr.com> wrote in message
news:%23o4XesQfFHA.3164@TK2MSFTNGP15.phx.gbl...
> The OR statement can be combined with the IF statement as
> follows.........adjust to suit.........
>
> =IF(OR(K53=301,K53=901),"11517",IF(OR(K53=701,K53=801),"40323", etc
> etc.......
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "ana_15825 - ExcelForums.com" <ana_15825@hotmail-dot-com.no-spam.invalid>
> wrote in message news:OmKagePfFHA.2384@TK2MSFTNGP15.phx.gbl...
> > I am very new to Excel (I've only used it a few times before this). I
> > have already figured out (mostly by accident) how to get my worksheet
> > to give me the following:
> >
> > One cell is for input of the company Division numbers. When a number
> > is entered, the employee number and manager name of that Division
> > appear in other cells. If nothing is entered, I want it to remain
> > blank.
> >
> > My current formula for six of the employee numbers and the "blank"
> > is:
> >
> >
>
=IF(K53=301,"11517",IF(K53=901,"11517",IF(K53=701,"40323",IF(K53=801,"40323"
> ,IF(K53=601,"99253",
> > IF(K53=401,"41723",IF(K53=""," ")))))))
> >
> > Since I can only nest seven things, I can't get the seventh or eighth
> > manager into the formula and still keep the blank option. However,
> > currently three of the managers are each overseeing two or three
> > Divisions each, so I'd like to add an OR statement in there. I'd like
> > it to say that if K53 has either 301 or 901 to put employee 11517, if
> > K53 is 701 or 801 to put employee 40323, if K53 is 501 or 503 or 685
> > to put employee 49325, if K53 is 662 to put employee 05498, and keep
> > the rest of the IF statements as they are. Division 662 is a special
> > exceptions thing, though, so rarely used and can be left out if need
> > be.
> >
> > I don't know how to set up the formula though. I tried doing just one
> > of the managers and thought I had it, but keep getting "FALSE" when I
> > test it. Can any more experienced person help me get a formula that
> > works (with or without the 662 guy).
> >
> > Thanks.
> >
>
>