VLookup Or Other To Match Data.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,082
Office Version
365, 2019, 2016
Platform
Windows
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
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
ABCDEFGHI
1
2
3
4Name306090120150180210240
5Person 1£25.00£55.00£75.00£100.00£125.00£150.00£175.00£200.00
6Person 2£25.00£50.00£70.00£95.00£120.00£145.00£170.00£190.00
7Person 3£25.00£50.00£70.00£95.00£120.00£145.00£170.00£190.00
8
Sheet1


Example Workbook.xlsx
ABCDEF
1
2
3Person 107-Sep-208:30 AM9:30 AM1.0055
4Person 207-Sep-209:30 AM11:00 AM1.5070
5Person 108-Sep-208:00 AM10:00 AM2.00100
6
Sheet2
Cell Formulas
RangeFormula
E3:E5E3=((D3-C3+(D3 < C3))*24)
F3:F5F3=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

New Member
Joined
Apr 4, 2017
Messages
36
Example Workbook.xlsx
ABCDEFGHIJ
1Name30 Mins45 Mins60 Mins90 Mins120 Mins150 Mins180 Mins210 Mins240 Mins
2Person 1£25.00£40.00£55.00£75.00£100.00£125.00£150.00£175.00£200.00
3Person 2£25.00£35.00£50.00£70.00£95.00£120.00£145.00£170.00£190.00
4Person 3£25.00£38.00£50.00£70.00£95.00£120.00£145.00£170.00£190.00
5Person 4£25.00£37.00£50.00£70.00£95.00£120.00£145.00£170.00£190.00
6Person 5£25.00£39.00£50.00£70.00£95.00£120.00£145.00£170.00£190.00
Sheet1
 

fintail99

New Member
Joined
Apr 4, 2017
Messages
36
Example Workbook.xlsx
ABCDEF
1NameDateStartEndHrsAmount
2Person 107-Sep-208:30 AM9:30 AM1.00
3Person 207-Sep-209:30 AM11:00 AM1.50
4Person 108-Sep-208:00 AM10:00 AM2.00
Sheet2
Cell Formulas
RangeFormula
E2:E4E2=((D2-C2+(D2 < C2))*24)
Cells with Data Validation
CellAllowCriteria
A2List=Sheet1!$A$2:$A$12
A3:A24List=Sheet1!$A$2:$A$16
 

fintail99

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
You will need to change the headers on sheet1 to 30,60,90 etc rather than 30 Mins, 60 Mins etc.
 

fintail99

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
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

New Member
Joined
Apr 4, 2017
Messages
36
Hi Fluff, this works brilliantly. Many thanks for your help and apologies again for posting elsewhere simultaneously. Best wishes, Ketan
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,471
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,856
Messages
5,507,748
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top