VLookup Or Other To Match Data.

fintail99

New Member
Joined
Apr 4, 2017
Messages
38
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try this

Book1
ABC
1Name1Name2
20.5Date
3£25.009:00 AM
4110:00 AM
5£55.001
61.565
7£75.00
82
9£100.00
103
11£125.00
124
13£150.00
145
15£175.00
166
17£200.00
18Name2
190.5
20£25.00
211
22£65.00
231.5
24£75.00
252
26£100.00
273
28£125.00
294
30£150.00
315
32£175.00
336
34£200.00
Sheet1
Cell Formulas
RangeFormula
C6C6=INDEX(OFFSET($A$1,MATCH(C1,A:A,0)+1,0,16),MATCH(C5,OFFSET($A$1,MATCH(C1,A:A,0),0,16),0))
 

fintail99

New Member
Joined
Apr 4, 2017
Messages
38
Hi Alan,

Thanks for your response. If the forum allows, would it be ok for me to upload an Excel workbook here, for the suggested formula to be assigned? I need it to pickup data from one sheet and apply the result to another (within the same workbook).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,183
Office Version
  1. 365
Platform
  1. Windows
Cross posted

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply all relevant links
 

fintail99

New Member
Joined
Apr 4, 2017
Messages
38

ADVERTISEMENT

My apologies. I have posted the same elsewhere with the following link. No firm solution obtained yet. Apologies once again - I wasn't aware of this rule.


Kind regards,
Ketan
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,183
Office Version
  1. 365
Platform
  1. Windows
What should happen if someone works 45 minutes?
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Alan,

Thanks for your response. If the forum allows, would it be ok for me to upload an Excel workbook here, for the suggested formula to be assigned? I need it to pickup data from one sheet and apply the result to another (within the same workbook).
well, i'd moved that into 2 sheets

Book1
A
1Name1
20.5
3£25.00
41
5£55.00
61.5
7£75.00
82
9£100.00
103
11£125.00
124
13£150.00
145
15£175.00
166
17£200.00
18Name2
190.5
20£25.00
211
22£65.00
231.5
24£75.00
252
26£100.00
273
28£125.00
294
30£150.00
315
32£175.00
336
34£200.00
Sheet1


Book1
A
1Name2
2Date
39:00 AM
410:00 AM
51
665
Sheet2
Cell Formulas
RangeFormula
A6A6=INDEX(OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!A:A,0)+1,0,16),MATCH(A5,OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!A:A,0),0,16),0))
 

fintail99

New Member
Joined
Apr 4, 2017
Messages
38
Dear Sir/Madam, Moderator, I intended no harm nor offence, nor to waste anyone's valuable time. I am merely looking for assistance to a problem.

After I posted on this forum today, I remembered that I did not receive any assistance to my previous query a few months ago. This could be due to my own error in explaining that circumstance. Therefore, I took the approach to post elsewhere too. There was no malice intended.

If you wish to help, then kindly do so. If not, refrain from lecturing me about what happens if someone works any amount of time. I'm really not looking for a debate on this matter. If you have more free time to ask such questions, please direct them elsewhere. Sincere apologies once again for any inconvenience caused to you and to all.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,183
Office Version
  1. 365
Platform
  1. Windows
If you wish to help, then kindly do so.
I'm more than happy to help, but can you answer my question in post#6?

If not, refrain from lecturing me about what happens if someone works any amount of time.
I haven't lectured you, I simply pointed out that you need to supply links when you cross post, you have now done that so all is good.
 

fintail99

New Member
Joined
Apr 4, 2017
Messages
38
Thank you!

I hadn't previously considered 45 minutes as this isn't currently an option. However, I think it should be included for future reference. The monetary amount is unknown at present, but could be £40.

Would it be allowable for me to upload a sample spreadsheet? I have attempted AlanY's suggestion, but can't make it work most likely due to my own error.
 

Forum statistics

Threads
1,136,850
Messages
5,678,115
Members
419,746
Latest member
tysonboy82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top