# VLookup Or Other To Match Data.

1. ## 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. ## Re: VLookup Or Other To Match Data.

Hi there,

Thank you, please find attached a sample file.

3. ## 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

4. ## 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. ## Re: VLookup Or Other To Match Data.

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.

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

6. ## 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)

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

#### 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