Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Lookup on different page (Excel 9.0.3821)

    I have a spreadsheet where the data in column B has a corresponding number in column A.

    I have set up a lookup the data that needs to be matched on part of the spreadsheet on what is sheet 2 of the spreadsheet. The formula is
    =LOOKUP(C18,$C$43:$C$203,$B$43:$B$203)
    what do I need to do add where I need to repeat this formula on sheet 3 to 7 to refer sheets 3 to 7 back to the data on sheet 2? Adding 'sheet2' to the formula gets it looking for a new file, not the correct sheet

    Will

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup on different page (Excel 9.0.3821)

    Try:

    <pre>=LOOKUP(C18,Sheet2!$C$43:$C$203,Sheet2!$B$43: $B$203)
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup on different page (Excel 9.0.3821)

    Thanks for the response Legare. I can't get it to work after a quick re-try, but I shall look at the issue further this weekend

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup on different page (Excel 9.0.3821)

    Why not simply point and click to the proper range? Excel should get the right syntax then.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lookup on different page (Excel 9.0.3821)

    Now I've got it. Thanks guys!

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Location
    London, Gtr London, England
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Supplemental Q - Lookup on diff't page(9.0.3821)

    I am pleased to have gotten this working w. assistance.

    Two supplementals

    e.g.

    Fruit Stock number
    Apples green 1001
    Apples red 1200
    Blackberries 1150
    Cranberries 1300

    First.
    Just for neatness, at cell where the variable data, which is always text, is entered, the corresponding cell which will show up the result shows
    #NA
    when the is blank.
    Is there a quick workaround?

    Second
    How do I set the lookup formula so that it only shows exact matches with the data in the left column

    For example
    if some enters
    "apples"
    or
    "bananas"
    the lookup feature gives the nearest corresponding stock number, which is not what I want.

    This is the formula I use, BTW
    =LOOKUP(C8,'Page 1'!$C$43:$C$187,'Page 1'!$B$43:$B$187)

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Supplemental Q - Lookup on diff't page(9.0.3821)

    If you use VLOOKUP, there is a fourth argument that forces exact matches. Look in Help.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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