Lookup Function

[xxnonexnonexx]

I need some help with doing a LOOKUP in a spreadsheet.

Spreadsheet #1 has data for 3 shifts of employees and their daily totals of
selling widgets.

If the worked and sold widgets they have a value > 0
If the did NOT work they are blank

Column C3 to C33 has a list of the employee names, Billy Ray, Bobbi Jo, Mo,
Larry, Curly, Schemp, etc.... And D3 to Z33 is a range that contains the months
daily widget sales totals for each person.

I use the MIN & MAX functions to find the high & low for the ENTIRE day, and for
EACH SHIFT. I want to then do a look up to have a cell that shows the NAME of
the person with the high & low for the day, and each shift.

Example, where [ ] indicates a cell

[Daily High] [Mo] [200]
[Daily Low] [BobiJo] [2]


[Day Shift High] [Billy Ray] [150]
[Day Shift Low] etc..

I also would like to ADD up the number of times EACH employee is either HIGH or
LOW and keep track of it. So that it creates totals for each employee like:

High Low
Mo 4 10
Larry 10 3
Curly 2 1

VLOOKUP and HLOOKUP ? ?

Any assistance and guidance on doing this would be greatly appreciated.

Thank you in advance.

 

[Don Guillett]

try this where the numbers start in a1 and go down and the employee is in
the next column to the right. You are finding the max, using match to find
the row and offset to find the value.

=OFFSET(A1,MATCH(MAX(A1:A10),A1:A10)-1,1)

 

[Don Guillett]

=OFFSET(A1,MATCH(MAX(A1:A10),A1:A10)-1,1)
use this instead where ,0 is in the match function.
=OFFSET($A$1,MATCH(MAX($A$1:$A$10),$A$1:$A$10,0)-1,1)

--
Don Guillett
SalesAid Software
(e-mail address removed)

try this where the numbers start in a1 and go down and the employee is in
the next column to the right. You are finding the max, using match to find
the row and offset to find the value.

=OFFSET(A1,MATCH(MAX(A1:A10),A1:A10)-1,1)

--
Don Guillett
SalesAid Software
(e-mail address removed)

I need some help with doing a LOOKUP in a spreadsheet.

Spreadsheet #1 has data for 3 shifts of employees and their daily totals of
selling widgets.

If the worked and sold widgets they have a value > 0
If the did NOT work they are blank

Column C3 to C33 has a list of the employee names, Billy Ray, Bobbi Jo, Mo,
Larry, Curly, Schemp, etc.... And D3 to Z33 is a range that contains the months
daily widget sales totals for each person.

I use the MIN & MAX functions to find the high & low for the ENTIRE day, and for
EACH SHIFT. I want to then do a look up to have a cell that shows the

NAME

of
the person with the high & low for the day, and each shift.

Example, where [ ] indicates a cell

[Daily High] [Mo] [200]
[Daily Low] [BobiJo] [2]


[Day Shift High] [Billy Ray] [150]
[Day Shift Low] etc..

I also would like to ADD up the number of times EACH employee is either HIGH or
LOW and keep track of it. So that it creates totals for each employee like:

High Low
Mo 4 10
Larry 10 3
Curly 2 1

VLOOKUP and HLOOKUP ? ?

Any assistance and guidance on doing this would be greatly appreciated.

Thank you in advance.

 

[xxnonexnonexx]

=OFFSET(A1,MATCH(MAX(A1:A10),A1:A10)-1,1)
use this instead where ,0 is in the match function.
=OFFSET($A$1,MATCH(MAX($A$1:$A$10),$A$1:$A$10,0)-1,1)

Thank you. Solved the problem.

 

[Don Guillett]

glad to help

--
Don Guillett
SalesAid Software
(e-mail address removed)