+ Reply to Thread
Results 1 to 4 of 4

vlookup ? need help for comparing datas in different sheets

  1. #1
    Registered User
    Join Date
    08-08-2008
    Location
    singapore
    Posts
    7

    vlookup ? need help for comparing datas in different sheets

    sheet 1
    Models Price Received date

    xbox 88.45 1/1/2015
    xbox 99.00 1/6/2015
    ps3 92.25 1/2/2015
    nin 90.25 1/3/2015
    nin 94.00 1/5/2015

    sheet 2
    Models FOB CFR

    xbox 99.00 1.6
    ps3 92.25 1.6
    nin 94.00 1.6


    sheet 1 will be a master list for new/updated prices received and will keep adding. so price and date is the determining factor.
    sheet 2 will be where i do the calculation, adding in the cost of CFR into price.

    i want to the formula to automate this process rather than i having to manually enter the last price.
    ultimately i want to check for the latest updated price from the list and get the last price and this is via a comparison using models.
    eg. i want to get the price for xbox. the return should be 99.00 and not 88.45. so 99.00 + 1.6.


    im thinking vlookup,max,if.. but i cant get it to work.

    please help.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: vlookup ? need help for comparing datas in different sheets

    This is a little hard to explain, would be simpler a sample workbook. This will bring back the price of the LAST entry in the table marked "xbox", it's not using dates, it is using table order. You can replace "xbox" with a reference to an adjacent cell, of course.

    =LOOKUP(2, 1/(Sheet1!$A$1:$A$300="xbox"), Sheet1!$B$1:$B$300)

    I'm using a range of 300 rows, use a whatever is reasonable to you, do not try to use an entire blank column... eek.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-08-2008
    Location
    singapore
    Posts
    7

    Re: vlookup ? need help for comparing datas in different sheets

    i am not understanding but it works to my requirement.

    thanks

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: vlookup ? need help for comparing datas in different sheets

    Yeah, that's a "trick" formula, just know WHAT it does, not so important to initially understand how it does it. It basically means, "find the LAST row in the first column that matches our text string, then bring back the matching row value from the other column"


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. VBA for loop and Compare od datas in two different sheets
    By sarajun_88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-01-2014, 06:33 AM
  2. Vlookup in different sheets comparing columns
    By azzyd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-25-2014, 11:25 PM
  3. datas between sheets
    By mertserveren in forum Excel General
    Replies: 5
    Last Post: 02-28-2014, 08:31 AM
  4. Comparing datas of two sheets using macro
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-20-2009, 10:24 AM

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