+ Reply to Thread
Results 1 to 6 of 6

VLookup Or Other To Match Data.

  1. #1
    Registered User
    Join Date
    08-08-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    VLookup Or Other To Match Data.

    Hi there,

    I have data, as follows:

    Sheet1
    A1: Name
    B1: 0.5 (this relates to total time, see Sheet2 below)
    C1: 25.00 (this relates to the total time in cell B1)
    D1: 1.0 (this relates to total time, see Sheet2 below)
    E1: 55.00 (this relates to the total time in cell D1)
    F1: 1.5 (this relates to total time, see Sheet2 below)
    G1: 75.00 (this relates to the total time in cell F1)
    H1: 2.0 (this relates to total time, see Sheet2 below)
    I1: 100.00 (this relates to the total time in cell H1)
    (plus 4 more options following the suit above)

    Sheet2
    A1: Name (using a Data Validation List routine to pickup from Sheet1, A1:A100)
    B1: Date
    C1: Start Time (eg 9:00 AM)
    D1: End Time (eg 10:00 AM)
    E1: Total Time (eg 1.00, calculated by D1-C1)

    Assistance Required
    In Sheet2, I would like cell F1 to pickup the monetary amount that is stated in Sheet1, correlating to the total time. Eg, if Sheet2 End Time minus Start Time = 1.00, I would F1 to show the amount stated in Sheet1 cell E1.

    Please note that for each line of data (each person), the monetary amount that correlates to Total Time varies from person to person.

    Any assistance would be appreciated.

    Kind regards,
    Ketan

  2. #2
    Registered User
    Join Date
    08-08-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: VLookup Or Other To Match Data.

    Hi there,

    Thank you, please find attached a sample file.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    250

    Re: VLookup Or Other To Match Data.

    F3 cell , Array formula
    HTML Code: 
    =INDEX(Sheet1!$B$5:$I$7,MATCH(A3,Sheet1!$A$5:$A$7,),MATCH(((D3-C3)*1440)+1%,--LEFT(Sheet1!$B$4:$I$4,FIND(" ",Sheet1!$B$4:$I$4))))
    HTML Code: 
    =VLOOKUP(A3,Sheet1!$A$4:$I$15,MATCH(((D3-C3)*1440)+1%,--LEFT(Sheet1!$B$4:$I$4,FIND(" ",Sheet1!$B$4:$I$4)))+1,)
    E3 cell , if you want hour , formula
    HTML Code: 
    =((D3-C3)*1440)/60
    If you want minutes , formula
    HTML Code: 
    =(D3-C3)*1440
    1440 = 24*60
    Attached Files Attached Files
    Last edited by wk9128; 09-07-2020 at 09:29 AM.

  4. #4
    Registered User
    Join Date
    08-08-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: VLookup Or Other To Match Data.

    Hi there,

    I've tried both the array formula and HTML code, but both return "N/A".

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,269

    Re: VLookup Or Other To Match Data.

    crossposted: https://www.mrexcel.com/board/thread.../#post-5551470

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future

  6. #6
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    797

    Re: VLookup Or Other To Match Data.

    Is this what you're looking for? (in F1 and copied down)

    =INDEX(Sheet1!$B$5:$I$7,MATCH($A3,Sheet1!$A$5:$A$7,0),MATCH($E3*60,Sheet1!$B$3:$I$3,1))

    By they way, try changing your formula in E3:E5 to:
    =ROUND((D3-C3+(D3 < C3))*24,2)
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

+ 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: 1
    Last Post: 08-22-2020, 04:47 PM
  2. VBA vlookup to match 3 set of data
    By Afpire93 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-28-2019, 11:35 AM
  3. Match the data with the help of Vlookup
    By AVG123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-05-2018, 02:11 AM
  4. [SOLVED] how do I vlookup a cell with mulitple data to match another another multiple data,
    By kemi2299 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-13-2013, 08:21 AM
  5. Best way to Match Data i.e Vlookup
    By johnny_p in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2013, 12:18 PM
  6. How to use VLOOKUP to match the data?
    By kalucharan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-30-2012, 02:26 AM
  7. Trying to use a VLOOKUP to match data...
    By JonnyNYC90 in forum Excel General
    Replies: 3
    Last Post: 06-27-2011, 11:23 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