Results 1 to 10 of 10
Thread: If formula (XP)

20030116, 20:20 #1
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
If formula (XP)
Hi  I know this can be done  either stupidity or brain freeze  can't figure it out.
Column A will have student's last names  I need a formula in Column B that will look to see whether the letters in the last name fall between, for instance, Martinez and Pei, then return a specific counselor's name.
Can anyone help me, please?

20030116, 20:37 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: If formula (XP)
Try a variant of this. Pick your true and not true statement.
=if(AND(UPPER(A1)>"MARTINEZ",UPPER(A1)<"PEI"),"Thi s is a true statement", "Not a true statement")
Steve

20030116, 20:46 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: If formula (XP)
If I understand that you are assigning counselor by student last name alpha, create a counselor table like this (quick and dirty):
<table border=1><td></td><td align=center>AA</td><td align=center>AB</td><td align=center>AC</td><td align=center>AD</td><td align=center valign=bottom>8</td><td valign=bottom>From </td><td valign=bottom>To</td><td valign=bottom>Code</td><td valign=bottom>Counselor</td><td align=right valign=bottom></td><td align=center valign=bottom>9</td><td valign=bottom>A</td><td valign=bottom>J</td><td align=right valign=bottom>=CODE(AB9)</td><td valign=bottom>Counselor A</td><td align=center valign=bottom>10</td><td valign=bottom>K</td><td valign=bottom>P</td><td align=right valign=bottom>=CODE(AB10)</td><td valign=bottom>Counselor B</td><td align=center valign=bottom>11</td><td valign=bottom>Q</td><td valign=bottom>Z</td><td align=right valign=bottom>=CODE(AB11)</td><td valign=bottom>Counselor C</td></table>
And then use the formula:
=VLOOKUP(CODE(LEFT(A1,1)),$AC$9:$AD$11,2)
(May need tweaking.)John ... I float in liquid gardens
UTC 7ąDS

20030116, 23:02 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (XP)
I think that VLOOKUP is a better choice that IF since you can only next IF to 7 levels. You could create a table like this, lets say on Sheet2!A1:B7
<table border=1><td> </td><td>A</td><td>B</td><td>1</td><td>A</td><td>Councelor 1</td><td>2</td><td>Dogan</td><td>Councelor 2</td><td>3</td><td>Goode</td><td>Councelor 3</td><td>4</td><td>Martinez</td><td>Councelor 4</td><td>5</td><td>Pei</td><td>Councelor 5</td><td>6</td><td>Smith</td><td>Councelor 6</td><td>7</td><td>ZZZZZZZZZZZZ</td><td> </td></table>
Then you could use a formula like this:
<pre>=VLOOKUP(A1,Sheet2!$A$1:$B$7,2,TRUE)
</pre>
Legare Coleman

20030122, 21:09 #5
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (XP)
Thanks for the reply  good idea but i don't think I was specific enough (as usual). I've attached a sample list of student names in Column A  Column B is where I want the formula that will look at the student's name, then at the variables for counselor assignment, and return the appropriate counselor's name. Columns C and D are the alphabet spreads and corresponding counselor's name. I know you can only nest 7 but I figure I could make the "if none is true, return this" be the 8th counselor's name. Thanks again for any help you can provide.

20030122, 21:10 #6
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (XP)
Thanks for the reply  good idea but i don't think I was specific enough (as usual). I've attached a sample list of student names in Column A  Column B is where I want the formula that will look at the student's name, then at the variables for counselor assignment, and return the appropriate counselor's name. Columns C and D are the alphabet spreads and corresponding counselor's name. I know you can only nest 7 but I figure I could make the "if none is true, return this" be the 8th counselor's name. Thanks again for any help you can provide

20030122, 21:17 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: If formula (XP)
=VLOOKUP(A2,$C$2:$D$9,2)
can be copied down column B
Though you must GET rid of AVID in that entry.
Perhaps use AAvicz = Quick
Avid = Malloy
AvidaCall = Quick
...
RomfSec = Malloy
Steve

20030122, 21:36 #8
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (XP)
PERFECT!!!!! Thank you, thank you, thank you.

20030123, 01:52 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (XP)
It looks like Steve beat me to it and solved your problem.
Legare Coleman

20030123, 17:07 #10
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If formula (XP)
Yep  but I still really appreciate all the efforts to make my life easier.
Ta  have a great day
Linda