# VLookup Or Other To Match Data.

#### fintail99

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

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

#### fintail99

##### New Member

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
What should happen if someone works 45 minutes?

#### AlanY

##### Well-known Member

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

Replies
0
Views
43
Replies
2
Views
62
Replies
1
Views
126
Replies
4
Views
50
Replies
4
Views
57

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