+ Reply to Thread
Results 1 to 10 of 10

Looking up values.

  1. #1
    Registered User
    Join Date
    07-11-2012
    Location
    Massachusetts,US
    MS-Off Ver
    Excel 2010
    Posts
    44

    Looking up values.

    lookup.xlsx

    Hello I want to take the prices from sheet2 and put them on sheet1 but the date and material number has to match.
    Thank You!

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Looking up values.

    gjergji,

    In Sheet1 cell B2 and copied over and down to M290:
    Please Login or Register  to view this content.

    I don't know why your month name headers have a fake space (character code 160) at the end of them.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    07-11-2012
    Location
    Massachusetts,US
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Looking up values.

    can you give me a formula if you took out the spaces from the months

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Looking up values.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-11-2012
    Location
    Massachusetts,US
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Looking up values.

    Quote Originally Posted by tigeravatar View Post
    Please Login or Register  to view this content.
    But this gave me the sum I want to get the actual price or at least an average and the sum did even give me all the prices it missed some

    ---------- Post added at 02:32 PM ---------- Previous post was at 02:29 PM ----------

    didn't* i meant

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Looking up values.

    You have multiple items in the same month. The only criteria you give it is the month. How is it supposed to know which of the many items to return?

    Also, it did not miss any that I could find, it summed all that were appropriate based on the code and month.

    If you'd prefer an average instead of a sum, use this array formula. Note that array formulas must be confirmed with Ctrl+Shift+Enter and not just Enter, that's how the formula gets the curly braces {} around it. Do not try to add those yourself:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-11-2012
    Location
    Massachusetts,US
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Looking up values.

    thats why i combined the material with the month so there would be a unique code created

    ---------- Post added at 02:40 PM ---------- Previous post was at 02:38 PM ----------

    and i did sumif/countif and i got the average but the problem is that not all the values show up for example material code 300006

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Looking up values.

    gjergji,

    Directly from the attachment in your first post, Sheet2, first 10 rows:

    MATERIALCODE# REC'D DATE PRICE/LBS.
    300025 04/09/12 $0.00
    300001 04/04/12 $1.01
    300071 04/05/12 $1.69
    300089 04/04/12 $1.26
    300202 05/02/12 $2.25
    300001 04/06/12 $1.01
    300112 04/23/12 $0.84
    300067 04/26/12 $0.84
    300067 04/26/12 $0.84


    300001 and 300067 are both repeated in the month of April. That is why I was adding them together.
    As for 300006 not working, it worked just fine for me.

    Attached is a modified version of your posted workbook. I have removed the fake spaces from each month header in Sheet1. The formula in cell B2 and copied over and down is this array formula (see my note about array formula in my last post):
    Please Login or Register  to view this content.
    I have highlighted cell E8 which shows the value of 300006 in April (its only occurrence) of 1.045. That is the correct value. It is pulling it from row 66 of Sheet2. It looks like $1.05 because it has been formatted as currency with 2 decimal places. If you were to format that cell as General, you'll notice that it is actually 1.045
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-11-2012
    Location
    Massachusetts,US
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Looking up values.

    thank you very much for taking your time to do this i really appreciate it ill try to work it into my actual file. The file i have provided is only a small part of the file I'm using.

  10. #10
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Looking up values.

    You're very welcome, and best of luck to you

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1