IF Statement with Vlookup, largest amount

[calinini]

Hello!

I'm hoping for help on a formula.

Here is my source data:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

Employee ID

Name

JobCode

Jobtitle

Store Number

Secondary Store

Hire Dt

Rehire Dt

Termination

Disciplinary

Start Date

End Date

Days

LOA

Annual Rate

2

1234

EEA

01574

OTSA

1021

10/22/16

12/14/18

12/14/18

12/31/18

18

0

$11,700.00 <tbody> </tbody>

3

2345

EEB

01574

OTSA

1107

7/6/93

2/5/18

2/19/18

2/5/18

2/19/18

15

0

$11,700.00 <tbody> </tbody>

4

3456

EEC

01574

OTSA

1131

4/30/1979 <tbody> </tbody>

5/22/2014 <tbody> </tbody>

01/01/2018

3/31/18

90

0

$11,502.40 <tbody> </tbody>

5

3456

EEC

01574

OTSA

1131

4/30/1979

5/22/2014 <tbody> </tbody>

4/1/2018

12/31/18

275

0

$11,762.40 <tbody> </tbody>

6

4567

EED

01538

SM

4103

11/14/2005 <tbody> </tbody>

01/01/2018

09/16/18

259

0

$36,358.40 <tbody> </tbody>

7

4567

EED

01574

OTSA

4103

11/14/2005

9/17/18

12/31/18

106

0

$10,608.00 <tbody> </tbody>

8

5678

EEE

01574

OTSA

3023

4/17/1995 <tbody> </tbody>

01/01/2018

3/31/18

90

0

$26,228.80 <tbody> </tbody>

9

5678

EEE

01574

OTSA

3023

4/17/1995

4/01/18

12/31/2018 <tbody> </tbody>

275

$29,120.00

<tbody>
</tbody>

Then I have another worksheet that consolidates rows and gives me the total number of days an employee worked the same position in the same store. In the example below, you can see that Rows 4&5 above are now one single row below, #4 as well as rows 8&9 above, are now row 7 below. Employee ID is a unique identifier.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Employee ID	Name	JobCode	Jobtitle	Store Number	Secondary Store	Hire Dt	Rehire Dt	Termination	Disciplinary	Days	LOA
2	1234	EEA	01574	OTSA	1021		10/22/16	12/14/18			18	0
3	2345	EEB	01574	OTSA	1107		7/6/93	2/5/18	2/19/18		15	0
4	3456	EEC	01574	OTSA	1131		4/30/1979	5/22/2014 <tbody> </tbody>			365	0
5	4567	EED	01538	SM	4103		11/14/2005 <tbody> </tbody>				259	0
6	4567	EED	01574	OTSA	4103		11/14/2005				106	0
7	5678	EEE	01574	OTSA	3023		4/17/1995 <tbody> </tbody>				365	0

<tbody>
</tbody>

The formula I need is the largest annual rate to be inputted on my second worksheet where the employee ID, Job Code, and Store Number are the same as the source data.

EXAMPLE:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee ID	Name	JobCode	Jobtitle	Store Number	Secondary Store	Hire Dt	Rehire Dt	Termination	Disciplinary	Days	LOA	Annual Rate
2	1234	EEA	01574	OTSA	1021		10/22/16	12/14/18			18		$11,700.00 <tbody> </tbody>
3	2345	EEB	01574	OTSA	1107		7/6/93	2/5/18	2/19/18		15		$11,700.00 <tbody> </tbody>
4	3456	EEC	01574	OTSA	1131		4/30/1979	5/22/2014 <tbody> </tbody>			365		$11,762.40 <tbody> </tbody>
5	4567	EED	01538	SM	4103		11/14/2005 <tbody> </tbody>				259		$36,358.40
6	4567	EED	01574	OTSA	4103		11/14/2005				106		$10,608.00
7	5678	EEE	01574	OTSA	3023		4/17/1995 <tbody> </tbody>				365		$29,120.00

<tbody>
</tbody>

I know it is a combination of IF and VLOOKUP's but I have zero room for error and my actual data is 5000+ Rows.

Thank you in advance for your help.

Sincerely,

Me.

 

[steve the fish]

Do you have MAXIFS on your excel version? Vlookup wont help you here.

 

[veejayc]

how about SUMIF function