1. ## 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.

2. ## 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

3. ## 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:

(May need tweaking.)

4. ## 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>

5. ## 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.

6. ## 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

7. ## 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 A-Avicz = Quick
Avid = Malloy
Avida-Call = Quick
...
Romf-Sec = Malloy

Steve

8. ## Re: If formula (XP)

PERFECT!!!!! Thank you, thank you, thank you.

9. ## Re: If formula (XP)

It looks like Steve beat me to it and solved your problem.

10. ## Re: If formula (XP)

Yep - but I still really appreciate all the efforts to make my life easier.

Ta - have a great day

Linda

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•