Results 1 to 10 of 10

Thread: If formula (XP)

  1. #1
    5 Star Lounger
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Uranium Lounger
    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

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

  5. #5
    5 Star Lounger
    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.
    Attached Files Attached Files

  6. #6
    5 Star Lounger
    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
    Attached Files Attached Files

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 A-Avicz = Quick
    Avid = Malloy
    Avida-Call = Quick
    ...
    Romf-Sec = Malloy

    Steve

  8. #8
    5 Star Lounger
    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.

  9. #9
    Uranium Lounger
    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

  10. #10
    5 Star Lounger
    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

Posting Permissions

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