+ Reply to Thread
Results 1 to 7 of 7

formula to work out the value of a cell from another sheet based on a unique number

Hybrid View

  1. 01-10-2011, 11:15 AM #1
    nick123
    nick123 is offline
    Registered User
    Join Date
    01-10-2011
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    3

    formula to work out the value of a cell from another sheet based on a unique number

    i have a spreadsheet with 3 sheets, if i start from sheet3 and work back,
    sheet3 will contain a long list of postcodes and customer numbers.
    sheet2 will contain the formulas to get the data from sheet1 but in sheet 1 some postcodes will be missing from time to time so what i want to be able to be able to do is get the postcodes from sheet3 by searching on the customer number then taking the postcode next to the customer number.
    sheet1 will contain postcodes and customer numbers but some of the postcodes will be missing.

    ive attached a example hopefully that will make a little more sense.
    Attached Files Attached Files
    Last edited by nick123; 01-11-2011 at 05:30 AM.
    Reply With QuoteRegister To Reply
  2. 01-10-2011, 11:22 AM #2
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: formula to work out the value of a cell from another sheet based on a unique numb

    Use VLOOKUP
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  3. 01-10-2011, 12:28 PM #3
    nick123
    nick123 is offline
    Registered User
    Join Date
    01-10-2011
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: formula to work out the value of a cell from another sheet based on a unique numb

    Quote Originally Posted by royUK View Post
    Use VLOOKUP
    ive tried vlookup which i dont think i could get working. when playing with formulas i managed to get "lookup" sort of working i used some thing like =LOOKUP(A12,AZ1:AZ10000,BA1:BA10000) all data was on the same sheet for this test but when looking at the data i noticed that the postcodes went out of sync after about 500 postcodes
    Reply With QuoteRegister To Reply
  4. 01-10-2011, 12:38 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: formula to work out the value of a cell from another sheet based on a unique numb

    You need to lock your ranges (rows only in this case) in place with $
    so this should work
    =LOOKUP(A12,AZ$1:AZ$10000,BA$1:BA$10000)

    VLOOKUP should work also
    =VLOOKUP(A12,$AZ$1:$BA$10000,2,FALSE)
    Hope that helps.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  5. 01-10-2011, 12:46 PM #5
    outofthehat
    outofthehat is offline
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    294

    Re: formula to work out the value of a cell from another sheet based on a unique numb

    You can use whole columns in formulae too. This performs the VLOOKUP on the whole of columns A & B on sheet3:
    =VLOOKUP(Sheet2!A2,Sheet3!A:B,2,FALSE)
    If you'd rather, select those two columns and name them (e.g. Cust_PCodes), then you can use the name in the formula:
    =VLOOKUP(Sheet2!A2,Cust_PCodes,2,FALSE)
    Use IF(ISBLANK(...)...) to decide whether or not to use Sheet 3:
    =(IF(ISBLANK(Sheet1!B2),VLOOKUP(Sheet2!A2,Sheet3!A:B,2,FALSE),Sheet1!B2)
    And finally, convert the answer to upper case for the postcode...
    =UPPER(IF(ISBLANK(Sheet1!B2),VLOOKUP(Sheet2!A2,Sheet3!A:B,2,FALSE),Sheet1!B2))
    Reply With QuoteRegister To Reply
  6. 01-11-2011, 05:34 AM #6
    nick123
    nick123 is offline
    Registered User
    Join Date
    01-10-2011
    Location
    uk
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: formula to work out the value of a cell from another sheet based on a unique numb

    thank you all for your answers i had spent a couple of days playing with this. +rep for all who replied.
    i went with outofthehat =(IF(ISBLANK(Sheet1!B2),VLOOKUP(Sheet2!A2,Sheet3!A:B,2,FALSE),Sheet3!B2)
    cheers all thank you.
    Reply With QuoteRegister To Reply
  7. 01-11-2011, 09:43 AM #7
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: formula to work out the value of a cell from another sheet based on a unique numb

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1