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
 

fintail99

New Member
Joined
Apr 4, 2017
Messages
38
Hello again!

The formula above does what we require it to do, but obviously shows #N/A until a time is entered. Is it possible for the resultant cell with monetary value to remain blank until start & end times are entered?

Thank you,
Ketan
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,423
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(AND(C2<>"",D2<>""),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)),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,423
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,487
Messages
5,548,351
Members
410,828
Latest member
A9Bosv3
Top