Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Using LOOKUP formulae (Excel 2003)

    Hi Loungers......I am having trouble using the LOOKUP wizard in Excel...I am attaching a sample s-sheet. There are 2 worksheets--one (NAME) shows staff names and I insert various jobsites under their names for certain days....on the 2dn worksheet (PLACE), I want a use a LOOKUP formula that will 'read' the assignments on the NAME sheet and insert the staff name under the appropriate jobsite for the proper day...so that people can search their jobsite by either location or name....the LOOKUP wizard doesn't seem to be too much of a wizard, mainly b/c it is hard to understand how it works...can someone give me a sample that I can use to ste this up, or point me to where I can learn LOOKUPs (horizontal or vertical...not sure which is needed here)...thank you.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using LOOKUP formulae (Excel 2003)

    <P ID="edit" class=small>(Edited by mbarron on 17-Apr-08 22:38. Forgot the formula)</P>Lookups aren't going to do the job. You need to use a combination of INDEX and MATCH.

    Without VBA you cannot have two people assigned to the same job on the same day. You also need to have the job name the same on both sheets. I've made changes to the original sheet and highlight the caveats.

    The formula in D4, which can be drug to the right and then down: (or down and then to the right)
    =IF(ISNA(INDEX(NAME!$D$2:$G$2,MATCH(D$3,NAME!$D3:$ G3,0))),"",INDEX(NAME!$D$2:$G$2,MATCH(D$3,NAME!$D3 :$G3,0)))
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using LOOKUP formulae (Excel 2003)

    Thank you for that...complicated formula, yet simple....I keep getting an error message (Cannot shift objects off sheet) when I try to hide some columns....any idea what that is all about--I have deleted, cleared etc everything in the columns and yet the message continues....

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using LOOKUP formulae (Excel 2003)

    i"m back mbarron.....just realized that I forgot to include in my original sample the fact that on occasion, a jobsite might be preceded by a single asterisk (or 2..or 3 asterisks) ...this triggers a color change.....but when that happens, the person's name won't show on the 2008Places sheet (b/c of the asterisk)....is there some way to have the formula that you used on the 2008Places sheet also read or recognize text that is preceded by 1 or more asterisks....maybe by using a SUBSTITUTE formula to strip the asterisk out on the Places worksheet?...I don't realy care about having the coloring come thru to the Places worksheet, and if I want that, I'll juts use the same cond. formatting ....thanks for your help witjh this...I've attached a modified book to show you what I mean...
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using LOOKUP formulae (Excel 2003)


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using LOOKUP formulae (Excel 2003)

    Enter the following formula in D22 on the 2008Place sheet as an array formula, i.e. confirm with Ctrl+Shift+Enter
    <code>
    =IF(ISNA(INDEX('2008NAME'!$D$10:$K$10,MATCH(D$10,S UBSTITUTE('2008NAME'!$D22:$K22,"*",""),0))),"",IND EX('2008NAME'!$D$10:$K$10,MATCH(D$10,SUBSTITUTE('2 008NAME'!$D22:$K22,"*",""),0)))
    </code>
    Note: the browser will probably insert line breaks in the formula, but it's one long formula.
    Fill down to D44, then right to column Q.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using LOOKUP formulae (Excel 2003)

    Or slightly shorter, still as an array formula:
    <code>
    =IF(ISNA(MATCH(D$10,SUBSTITUTE('2008NAME'!$D22:$K2 2,"*",""),0)),"",INDEX('2008NAME'!$D$10:$K$10,MATC H(D$10,SUBSTITUTE('2008NAME'!$D22:$K22,"*",""),0)) )
    </code>
    This formula tests ISNA directly on the result of MATCH instead of on the result of INDEX.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Using LOOKUP formulae (Excel 2003)

    Thank you for your help, Hans, and for the article on "Shifting objetcs" when hiding rows...have a good weekend,

Posting Permissions

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