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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,180
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
46,439
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
46,439
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,180
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
46,439
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,177
Messages
5,527,259
Members
409,754
Latest member
ekTZ

This Week's Hot Topics

Top