Vlookup Max Value

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
Glad we could help & thanks for the feedback
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

hscott82

New Member
Joined
Oct 30, 2019
Messages
33
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)
BCDJ
Employee IDNameHome LocationWorked Location
238324Smith, John002
502199Toms, Alex125
445882Walters, Jean223

ORIG (Sheet Name)
CDJU
EMPLOYEE IDNameHome LocationWorked Location
238324Smith, John002002
238324Smith, John002002
238324Smith, John002005
502199Toms, Alex125125
502199Toms, Alex125130
502199Toms, Alex125125
445882Walters, Jean223223
445882Walters, Jean223223
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
927
Office Version
365, 2010
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

New Member
Joined
Oct 30, 2019
Messages
33
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

Well-known Member
Joined
May 8, 2018
Messages
927
Office Version
365, 2010
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?
 
Last edited:

hscott82

New Member
Joined
Oct 30, 2019
Messages
33
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 HoursWorked Location
5.23225
9.05229
7.84226
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
927
Office Version
365, 2010
To what do those 3 lines apply?
 

hscott82

New Member
Joined
Oct 30, 2019
Messages
33
As an example say one Employee, so below those in Red are the Highest Hours and Different than the Home Location.

ORIG (Sheet Name)
CDJOU
EMPLOYEE IDNameHome LocationEarned HoursWorked Location
238324Smith, John0029.34002
238324Smith, John0028.64003
238324Smith, John00215.69005
502199Toms, Alex1255.75135
502199Toms, Alex1259.34130
502199Toms, Alex1255.50125
445882Walters, Jean223223
445882Walters, Jean223223
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
927
Office Version
365, 2010
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

Well-known Member
Joined
May 8, 2018
Messages
927
Office Version
365, 2010
I created a helper column W on the Orig sheet and filled down W2:
Code:
=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):
Code:
=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.
 

Forum statistics

Threads
1,089,636
Messages
5,409,456
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top