Vlookup Max Value

[hscott82]

Hello, I am trying to look up the Max Value (Paid Rate) from one sheet and return it to another sheet.

I have employees that are paid at a Base Rate, but sometimes are paid at a different rate. On 'Orig' Tab there can be a lot of Rows for employees and I need to find the MAX Paid Rate in Column P. It would be Higher than their Base Rate.

Sheet 1 (Research Tab)

B	C	I	K
Employee ID	Name	Base	Paid rate
0238324	Smith, John	$ 20.00
0502199	Toms, Alex	$ 9.75
0445882	Walters, Jean	$15.00

Sheet 2 (Orig Tab) The Paid Rates in RED are the Values I am looking at Returning to the Research Tab, because they are different than the Base Rate in column H.

C	D	H	N	P
EMPLOYEE ID	Name	Base Rate	Code	Paid Rate
0238324	Smith, John	20​	REG2	20​
0238324	Smith, John	20​	REG2	25​
0238324	Smith, John	20​	REG2	20​
0502199	Toms, Alex	9.75​	REG2	9.75​
0502199	Toms, Alex	9.75​	REG2	10.5​
0502199	Toms, Alex	9.75​	REG2	9.75​
0445882	Walters, Jean	15​	REG2	15​
0445882	Walters, Jean	15​	REG2	15​

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[hscott82]

Ok, I have another question I need to pull out of this set. Same data set, I need to pull if they worked at a Different location, different from their Home Location.

Research (Sheet Name)
B	C	D	J
Employee ID	Name	Home Location	Worked Location
238324	Smith, John	002
502199	Toms, Alex	125
445882	Walters, Jean	223

ORIG (Sheet Name)
C	D	J	U
EMPLOYEE ID	Name	Home Location	Worked Location
238324	Smith, John	002	002
238324	Smith, John	002	002
238324	Smith, John	002	005
502199	Toms, Alex	125	125
502199	Toms, Alex	125	130
502199	Toms, Alex	125	125
445882	Walters, Jean	223	223
445882	Walters, Jean	223	223

 

[kweaver]

How about this Conditional Formatting rule for U2:Uwhatever: =U2<>J2 and set it to bold and red font?

Probably use: =VLOOKUP(D2,Research!$C$1:$D$4,2,FALSE) for J2 (fill down).

 

[hscott82]

I could do that but similar to the formula you both helped me with before; I need to pull that "Other worked location" to the Research sheet.

These lists are very large and I need to automate pulling in the data. I tried to use a formula similar but can't get it to work right.

 

[kweaver]

What do you want to show on the Research sheet for those people? If they ever worked on a different-than-home, show that one?

How's this in J2 (fill down) on Research and format custom to be 000;;;
=SUMPRODUCT((B2=Orig!$C$2:$C$9)*(Orig!$U$2:$U$9<>D2)*(Orig!$U$2:$U$9))

But, could a person work on more than 1 non-home location? If so, then what?

 

[hscott82]

Yes, they can work at more than 1 Non-Home Location. (I just ran into that problem). If so then we have to look at Column O "Earned Hours" to use the location with the Most Hours.

For Example I would want to pull the Location 229 because it had the most hours.

Earned Hours	Worked Location
5.23	225
9.05	229
7.84	226

 

[kweaver]

To what do those 3 lines apply?

 

[hscott82]

As an example say one Employee, so below those in Red are the Highest Hours and Different than the Home Location.

ORIG (Sheet Name)
C	D	J	O	U
EMPLOYEE ID	Name	Home Location	Earned Hours	Worked Location
238324	Smith, John	002	9.34	002
238324	Smith, John	002	8.64	003
238324	Smith, John	002	15.69	005
502199	Toms, Alex	125	5.75	135
502199	Toms, Alex	125	9.34	130
502199	Toms, Alex	125	5.50	125
445882	Walters, Jean	223		223
445882	Walters, Jean	223		223

 

[kweaver]

GRIN...
So, for Smith, you want 005 and for Toms, you want 130?!

What happens when someone works non-home and the same number of hours at each of the non-home locations?
Did you think of that one?

 

[kweaver]

I created a helper column W on the Orig sheet and filled down W2:

=IF(MAXIFS(Orig!$O$2:$O$9,Orig!$C$2:$C$9,C2,Orig!$U$2:$U$9,"<>"&Research!D2)=O2,Orig!U2,0)

Then, on the Research sheet, filled down this ARRAY FORMULA (CTRL+Shift+Enter):

=SUM((B2=Orig!$C$2:$C$9)*(Orig!$U$2:$U$9<>D2)*(Orig!$W$2:$W$9))

and set the custom format to 000;;;

I'm sure there is a much simpler way, maybe using AGGREGATE or something else, but don't have time to clean this up right now.

If you can put in a helper column per the above, and have to place it elsewhere than W, adjust the formula on the Research sheet accordingly.