I want to use more than 7 nested if then statements

[Guest]

I've read the previous postings regarding the use of more than 7 if then
statements, and tried the following:
CELL D2 =IF(OR(B4=4,B4=5,B4=6,B4=14,B4=16),"Mike
Pelosi",IF(OR(B4=1,B4=2,B4=3,B4=13,B4=24),"Nick
Re",IF(OR(B4=7,B4=8,B4=22,B4=35),"Nicole Hughes",IF(OR(B4=20,B4=25,B4=9),"Kim
O'Connor", "D3"))))
CELL D3 =IF(OR(B4=19,B4=21,B4=26,B4=15,B4=18),"Justin
Eldredge",IF(OR(B4=23,B4=30,B4=34,B4=36),"Janet Ward",IF(OR(B4=11,B4=12),"Jen
Ferrarraccio")))
CELL D4 =IF(B4=D2),D2,D3
I am attempting to make the formula for column D (fromD4 down) assign a
"Person" to the cell based on the number located in column B. However, this
doesn't seem to be working. I would greatly appreciate some help!
Thanks,
Mike

 

[Biff]

Hi!

You can shorten those formulas by doing this:

=IF(OR(B4={1,2,3,4}..........

In the formula in cell D2 remove the quotes from around "D3" if "D3" is
really a reference to cell D3

The formula in cell D3 is missing a final Value_If_False argument. It's not
required but if the criteria in the formula aren't met it will default to
FALSE.

You might be better off using VLOOKUP.

Biff

"IF I only had a brain for IF statements" <IF I only had a brain for IF
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Max]

I'd switch to using VLOOKUP
to greatly simplify things and for ease of maintenance

In a new sheet, say, Sheet2, in A1:B36,
create the lookup reference table below
(Numbers in col A, Names in col B):

1 Nick Re
2 Nick Re
3 Nick Re
4 Mike Pelosi
5 Mike Pelosi
6 Mike Pelosi
7 Nicole Hughes
8 Nicole Hughes
9 Kim O'Connor
10
11 Jen Ferrarraccio
12 Jen Ferrarraccio
13 Nick Re
14 Mike Pelosi
15 Justin Eldredge
16 Mike Pelosi
17
18 Justin Eldredge
19 Justin Eldredge
20 Kim O'Connor
21 Justin Eldredge
22 Nicole Hughes
23 Janet Ward
24 Nick Re
25 Kim O'Connor
26
27
28
29
30 Janet Ward
31
32
33
34 Janet Ward
35 Nicole Hughes
36 Janet Ward
(etc)

Then in say, Sheet1, we could simply use in say, D4, either:

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

where a zero return would mean there's no name assigned as yet for the
number input in B4

or, perhaps use a better but slightly longer:

=IF(B4="","",IF(VLOOKUP(B4,Sheet2!A:B,2,0)=0,"Unassigned
#",VLOOKUP(B4,Sheet2!A:B,2,0)))

which returns: Unassigned #
instead of zeros for unassigned numbers input in B4

Just copy D4 down to return correspondingly for other inputs in B5, B6 ...
etc

Note that the 2nd formula does not trap for "non-existing"/ invalid numbers
which may be input in B4, for example: 40 or 0 or -2 (say). Such cases will
still return: #N/A

And if you want to trap for these as well,
then put in D4:

=IF(B4="","",IF(ISNA(VLOOKUP(B4,Sheet2!A:B,2,0)),"Invalid
#",IF(VLOOKUP(B4,Sheet2!A:B,2,0)=0,"Unassigned
#",VLOOKUP(B4,Sheet2!A:B,2,0))))

Invalid input numbers in B4 will return the phrase

 

[Max]

=IF(OR(B4={1,2,3,4}..........

You might be better off using VLOOKUP ..

... and which is exactly the response detailed in the other post <g>

 

[Shakespeare]

similarly to the first post I was trying to nest more than 7 statements
which isn't working.

I have a column of dates, and I'm trying to calculate the date usin
the Month function. However that function only returns a number and
want to display the month written out.

Here's the way I was going:

=IF((MONTH(A3))=7,"July",IF((MONTH(A4))=8,"August",IF((MONTH(A19))=9,"September",IF((MONTH(A23))=10,"October",IF((MONTH(A21))=11,"November",IF((MONTH(A26))=12,"December",IF((MONTH(A11))=1,"January",
No Month")))))))

I was going to try the vlookup function, however this spreadsheet i
setup as a "list" and vlookup says the table must be sorted i
ascending order... which might not be the case.

There has got to be an easier way to do this.

Thank you

 

[Max]

:
....

=IF((MONTH(A3))=7,"July",IF((MONTH(A4))=8,"August",IF((MONTH(A19))=9,"Septem
ber",IF((MONTH(A23))=10,"October",IF((MONTH(A21))=11,"November",IF((MONTH(A2
6))=12,"December",IF((MONTH(A11))=1,"January","
No Month"))))))) .....
There has got to be an easier way to do this.

Think we could try just something like:
=TEXT(A1,"mmmm")
where A1 contains a date

 

[Roger Govier]

In addition to Max's post which gives you the easy solution, you need to
know that VLOOKUP doesn't have to have a list of sorted data as you suggest.
If you use the 4th argument with VLOOKUP either FALSE or 0, then it will
work with unsorted lists.
=VLOOKUP(your_value,your_table,2,FALSE)

--
Regards

Roger Govier


"Shakespeare" <[email protected]>
wrote in message
news:[email protected]...

 

[Ragdyer]

If you have a "true", XL recognized date in a cell, and you wish to display
the month (or day), you can just format *that* cell, or any other cell to
display what you wish.

With
8/4/2005
in A1
You can format A1 to
mmmm
OR, in say B1, enter
=A1
And format B1 to
mmmm

 

[Guest]

Thanks Max, Biff, Roger and Shakespeare, for all of your help. Everything is
in working perfectly. Have a great day!
~Mike

 

[Max]

You're welcome, Mike !
Thanks for the feedback ..

 

[Shakespeare]

I knew there was a function, but just couldn't remember what it was. Th
text function works perfectly for my list.
Many Thanks,
JoAnn

 

[Max]

You're welcome, JoAnne !