Results 1 to 5 of 5

Thread: VLookup (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    VLookup (2000)

    I have searched, but to no avail!
    On worksheet 1, each cell in column A contains the 12 months of the year (in order). Column B contains the current market prices for a particular item I order, corresponding to that particular month. For example, column A, cell A1 contains "January" (in "mmmm yy" format), and cell B1 contains $5.00, the current price of the item I have ordered.
    Worksheets 2-13 are detailed monthly sheets corresponding to the individual months listed on worksheet 1. On each monthly worksheet, in cell A1 I have entered the respective month (in "mmmm yy" format). In rows 2 through 500 (if I need that many) I have listed orders I have placed for the item for which the current market price is listed on worksheet 1, and the price at which I placed the order.
    What I want to do is compare the difference between the current market price and the price at which I placed an order, per individual item on rows 2-500 on each worksheet 2-13.
    I know vlookup is involved, but I can't seem to piece all the parts together.
    Any help?
    Thanks,
    Jeff

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VLookup (2000)

    Hi Jeff,

    Lookups are always a bit tricky, but I think this is what you need.

    I set up Sheet1 as you indicated and entered all the dates as the first of the relevant month - even though the day does not show the dates must be entered consistently, as that is the real value used in the lookup, not the formatted display.

    Then I entered on Sheet2 - 1-Feb-03 in cell A1(Note it matches an actual value on Sheet1), in B2 I entered the price paid, and in B3 the lookup formula as such:

    =VLOOKUP(A1,Table,2,FALSE)

    =VLOOKUP(A1[The address of the date on Sheet2],Table[Range Sheet1 A1:B12],2[2nd col in Range "Table"],FALSE[You want exact values])

    This gave me the value expected from cell B2 on Sheet1 (Range Table)

    Good Luck, and please advise if there are still problems.

    Peter Moran

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLookup (2000)

    Thanks for your reply, Peter, but I didn't see an attachment.
    Jeff

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: VLookup (2000)

    Hi Jeff,

    Here is the attachment!

    Regards,

    Peter Moran
    Two heads are always better than one!!
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLookup (2000)

    Works great!
    Thanks.

Posting Permissions

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