VLookup Or Other To Match Data.

[fintail99]

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

 

[AlanY]

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.

you can try post your data here using MrExcel's XL2BB, Fluff's footnote has the info required

 

[Fluff]

We do not allow files to be uploaded to the site, but you can use the XL2BB add-in to post sample data, as AlanY has done.
As long as the times will always be a multiple of 30 minutes how about

Example Workbook.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3
4	Name	30	60	90	120	150	180	210	240
5	Person 1	£25.00	£55.00	£75.00	£100.00	£125.00	£150.00	£175.00	£200.00
6	Person 2	£25.00	£50.00	£70.00	£95.00	£120.00	£145.00	£170.00	£190.00
7	Person 3	£25.00	£50.00	£70.00	£95.00	£120.00	£145.00	£170.00	£190.00
8
Sheet1

Example Workbook.xlsx
	A	B	C	D	E	F
1
2
3	Person 1	07-Sep-20	8:30 AM	9:30 AM	1.00	55
4	Person 2	07-Sep-20	9:30 AM	11:00 AM	1.50	70
5	Person 1	08-Sep-20	8:00 AM	10:00 AM	2.00	100
6
Sheet2

Cell Formulas
Range	Formula
E3:E5	E3	=((D3-C3+(D3 < C3))*24)
F3:F5	F3	=INDEX(Sheet1!$B$5:$I$7,MATCH(A3,Sheet1!$A$5:$A$7,0),MATCH(ROUND(E3*60,0),Sheet1!$B$4:$I$4,0))

 

[fintail99]

Example Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	30 Mins	45 Mins	60 Mins	90 Mins	120 Mins	150 Mins	180 Mins	210 Mins	240 Mins
2	Person 1	£25.00	£40.00	£55.00	£75.00	£100.00	£125.00	£150.00	£175.00	£200.00
3	Person 2	£25.00	£35.00	£50.00	£70.00	£95.00	£120.00	£145.00	£170.00	£190.00
4	Person 3	£25.00	£38.00	£50.00	£70.00	£95.00	£120.00	£145.00	£170.00	£190.00
5	Person 4	£25.00	£37.00	£50.00	£70.00	£95.00	£120.00	£145.00	£170.00	£190.00
6	Person 5	£25.00	£39.00	£50.00	£70.00	£95.00	£120.00	£145.00	£170.00	£190.00
Sheet1

 

[fintail99]

Example Workbook.xlsx
	A	B	C	D	E	F
1	Name	Date	Start	End	Hrs	Amount
2	Person 1	07-Sep-20	8:30 AM	9:30 AM	1.00
3	Person 2	07-Sep-20	9:30 AM	11:00 AM	1.50
4	Person 1	08-Sep-20	8:00 AM	10:00 AM	2.00
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=((D2-C2+(D2 < C2))*24)

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet1!$A$2:$A$12
A3:A24	List	=Sheet1!$A$2:$A$16

 

[fintail99]

I tried the formula provided in Fluff's previous message, but the cell return "N/A". I've added XL2BB but not sure if what I have posted is correct!

 

[Fluff]

You will need to change the headers on sheet1 to 30,60,90 etc rather than 30 Mins, 60 Mins etc.

 

[fintail99]

OK, tried that, but no joy. Same result "N/A".

As an alternative, is it possible to have a formula to identify the hours in Sheet2 E2, E3, etc... and then if "1.0", pick data from Sheet1 D2, D3, etc... corresponding to the relevant person? With this, I can maintain static data in Sheet1 columns B, C, D, etc...

 

[Fluff]

The formula I supplied was based on the sample file you posted on ExcelForum, with the layout you have shown here it will be
=INDEX(Sheet1!$B$2:$J$7,MATCH(A2,Sheet1!$A$2:$A$7,0),MATCH(ROUND(E2*60,0),Sheet1!$B$1:$I$1,0))

 

[fintail99]

Hi Fluff, this works brilliantly. Many thanks for your help and apologies again for posting elsewhere simultaneously. Best wishes, Ketan

 

[Fluff]

Glad we could help & thanks for the feedback.