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​

 

[Joe4]

Are you using Office 365 or Excel 2019?

If so, then you can use MAXIFS.
See: How to use the Excel MAXIFS function | Exceljet

 

[kweaver]

If you have 365 or 2019:

=MAXIFS(Orig!$P$2:$P$9,Orig!$C$2:$C$9,B2)

Otherwise, this ARRAY FORMULA:

=MAX(IF(Orig!$C$2:$C$9=B2,Orig!$P$2:$P$9))

 

[DanteAmor]

Try this

varios 12feb2020.xlsm
	A	B	C	D	E
1		Employee ID	Name	Base	Paid rate
2		238324	Smith, John	20	25
3		502199	Toms, Alex	9.75	10.5
4		445882	Walters, Jean	15	Is no different
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=IF(SUMPRODUCT(MAX((Sheet2!C2:C9=B2)*(Sheet2!P2:P9)))>D2,SUMPRODUCT(MAX((Sheet2!C2:C9=B2)*(Sheet2!P2:P9))),"Is no different")

 

[hscott82]

Thank you, none of these are working for me.

@kweaver Yours return values on some, but not on all and not the Max (Higher Rate). All the other rows return -.
=MAXIFS(Orig!$P$2:$P$2000,Orig!$C$2:$C$2000,B5)



@DanteAmor Yours returns #N/A. I have corrected it to this for my sheet names and expanded the row #'s of P to 2000.
=IF(SUMPRODUCT(MAX((ORIG!C2:C2000=B5)*(ORIG!P2:P9)))>I5,SUMPRODUCT(MAX((ORIG!C2:C2000=B5)*(ORIG!P2:P2000))),"Is no different")

 

[DanteAmor]

IF(SUMPRODUCT(MAX((ORIG!C2:C2000=B5)*(ORIG!P2:P9)))>I5,SUMPRODUCT(MAX((ORIG!C2:C2000=B5)*(ORIG!P2:P2000))),"Is no different")

Failed to change that data, it should be like this:

varios 12feb2020.xlsm
	B	C						I	J
4	Employee ID	Name						Base	Paid rate
5	238324	Smith, John						20	25
6	502199	Toms, Alex						9.75	10.5
7	445882	Walters, Jean						15	Is no different
Sheet1

Cell Formulas
Range	Formula
J5:J7	J5	=IF(SUMPRODUCT(MAX((Orig!C2:C2000=B5)*(Orig!P2:P2000)))>I5,SUMPRODUCT(MAX((Orig!C2:C2000=B5)*(Orig!P2:P2000))),"Is no different")

 

[kweaver]

Seems to work for my Research sheet:

XLOOKUP_info.xlsm
	B	C	D	E	F	G	H	I	J	K
1	Employee ID	Name						Base		Paid rate
2	238324	Smith, John						20		25
3	502199	Toms, Alex						9.75		10.5
4	445882	Walters, Jean						15		15
Research

Cell Formulas
Range	Formula
K2:K4	K2	=MAXIFS(Orig!$P$2:$P$9,Orig!$C$2:$C$9,B2)

 

[hscott82]

Dante That worked better, however, there can be multiple rows for the same employees as in my first example. Your code is just returning "Is no different" I feel like it is just looking at the first Rate in the column.

 

[DanteAmor]

These are the data on sheet1, in what way are you not reviewing all employees?
You could provide your example.

varios 12feb2020.xlsm
	C	D				H						N	O	P
1	EMPLOYEE ID	Name				Base Rate						Code		Paid Rate
2	238324	Smith, John				20?						REG2		20
3	238324	Smith, John				20?						REG2		25
4	238324	Smith, John				20?						REG2		20
5	502199	Toms, Alex				9.75?						REG2		9.75
6	502199	Toms, Alex				9.75?						REG2		10.5
7	502199	Toms, Alex				9.75?						REG2		9.75
8	445882	Walters, Jean				15?						REG2		15
9	445882	Walters, Jean				15?						REG2		15
Orig

 

[hscott82]

Oh my goodness! I am so sorry, I just realized how MASSIVE this spreadsheet is. Both your formulas work! I just needed to expand P out to 25k rows!!!! WOW!!! I didn't even realize!!!! I am sorry but thank you both SO MUCH!!!!! This is amazing!!!