+ Reply to Thread
Results 1 to 3 of 3

vlookup with multiple results but only want latest

  1. #1
    Registered User
    Join Date
    12-29-2014
    Location
    cape town
    MS-Off Ver
    2007
    Posts
    2

    vlookup with multiple results but only want latest

    Hi
    I have the following excel sheets:

    SHEET1:
    This is my data sheet that refresh from external source

    A B C D
    1 Instance InventoryID Date Price
    2 001 200 2010/09/09 5.23
    3 002 201 2010/09/10 7.89
    4 003 202 2010/09/10 5.23
    5 004 202 2010/09/11 5.99
    6 005 200 2010/09/12 5.00
    7 006 201 2010/09/13 8.05

    PS: I have about 8000 InventoryID's and about 90000 Instances and need to use all information




    SHEET2:
    This sheet I want to check what stock item (InventoryID) I received at latest price (not highest)
    A B C
    1 InventoryID Date Price
    2 200 2010/09/12 5.00
    3 201 2010/09/13 8.05
    4 202 2010/09/11 5.99

    I tried with pivot table in the following manner:

    Used my (InventoryID) as "Row Labels"
    Used my (Price) as "Values"
    Tried using (Date) in "Column Labels" but excel says "USE LESS DATA" so I went without this

    I then try and set (Price) in "values" to report only the latest but I can only choose max/min/sum and this is not what I want....


    Please help
    Last edited by divan; 12-29-2014 at 08:38 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: vlookup with multiple results but only want latest

    Try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Where
    • Sheet1!C:C is you InventoryID column on sheet1
    • B2 is your InventoryID on sheet2
    • Sheet1!E:E is Price column on sheet1

  3. #3
    Registered User
    Join Date
    12-29-2014
    Location
    cape town
    MS-Off Ver
    2007
    Posts
    2

    Re: vlookup with multiple results but only want latest

    Thanks zbor!!
    works like a charm

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 05-09-2014, 06:03 AM
  2. [SOLVED] formula or vba help on vlookup to get latest date from multiple lookup value of SO's
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-16-2014, 09:48 AM
  3. Replies: 9
    Last Post: 11-28-2013, 05:20 PM
  4. Vlookup with multiple results
    By mschweinzger in forum Excel General
    Replies: 1
    Last Post: 04-04-2012, 03:34 PM
  5. VLOOKUP for multiple results
    By Steamboat in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-29-2005, 10:27 PM

Tags for this Thread

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