lookup Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-1542233%22%20slang%3D%22en-US%22%3Elookup%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542233%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20set%20containing%20employee%20names%20(in%20Column%20G)%20and%20their%20work-shifts%20(in%20Column%20C).%20Some%20shifts%20are%20assigned%20points.%20I%20need%20to%20be%20able%20to%20add%20those%20points%20up%20if%20the%20employee%20is%20listed%20twice%20on%20the%20same%20day%20where%20one%20shift%20may%20be%202%20points%20and%20another%20shift%20is%20on%20that%20same%20night.%20I%20found%20the%20XLOOKUP%20Function%20easy%20enough%20to%20use%20but%20run%20into%20trouble%2C%20like%20I%20said%2C%20when%20the%20employee%20appears%20twice%20on%20the%20same%20day.%20I%20created%20a%20formula%20to%20log%20the%20points%20for%20the%20shift%20in%20a%20separate%20column%20(in%20Column%20H).%20The%20employee%20name%20is%20used%20for%20the%20XLOOKUP%20Function%20to%20bring%20the%20value%20into%20another%20table.%3C%2FP%3E%3CP%3EOr%2C%20can%20I%20force%20the%20direction%20of%20the%20lookup%20function%20to%20start%20at%20the%20bottom%20of%20a%20column%20and%20go%20up%20instead%20of%20down%20since%20there%20can%20only%20be%20two%20occurrences%20of%20an%20employee%20on%20any%20given%20day%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1542233%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542284%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542284%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738095%22%20target%3D%22_blank%22%3E%40wdeets%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20you%20could%20use%20SUMIFS()%20instead%20of%20XLOOKUP()%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544359%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544359%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738095%22%20target%3D%22_blank%22%3E%40wdeets%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20FILTER()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546035%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738095%22%20target%3D%22_blank%22%3E%40wdeets%3C%2FA%3E%26nbsp%3B%2C%20sample%20with%20the%20solutions%20from%20the%20suggestions%20above...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a data set containing employee names (in Column G) and their work-shifts (in Column C). Some shifts are assigned points. I need to be able to add those points up if the employee is listed twice on the same day where one shift may be 2 points and another shift is on that same night. I found the XLOOKUP Function easy enough to use but run into trouble, like I said, when the employee appears twice on the same day. I created a formula to log the points for the shift in a separate column (in Column H). The employee name is used for the XLOOKUP Function to bring the value into another table.

Or, can I force the direction of the lookup function to start at the bottom of a column and go up instead of down since there can only be two occurrences of an employee on any given day?

 

Thank you!

3 Replies

Hello @wdeets,

 

Perhaps you could use SUMIFS() instead of XLOOKUP()?

@wdeets 

Or FILTER()

@wdeets , sample with the solutions from the suggestions above...