Finding a match within a match

[Lainey]

I have employees in column A, payroll info in column B. All payroll fields
are same for each employee (Fred and Tom both have gross wages) Rows are the
pay periods done by date. I need to locate Freds Gross Wage on a given date.
I cant seem to get the right functions/formulas nested together. Any help
greatly appreciated.

 

[T. Valko]

One way (possibly):

=SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10)

Need a better description of your layout.

 

[T. Valko]

Typo:

=SUMPRODUCT(--(A1:A1="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10)

Should be:

=SUMPRODUCT(--(A1:A10="Fred"),--(B1:B10=DATE(yyyy,m,d)),C1:C10)

 

[Lainey]

The "Register" worksheet looks like what is below but with about 10 types of
wages for each employee, approx 30 employees. I need to be able to pull to a
summary pay period sheet for 1/5/08 for certain lines. I was doing it with a
H or V Lookup and hard coding in the row or column number. Problem is,
adding a line, messes up all the rows and columns. I need it to look up
Freds Gross for 1/5/08, not necessarily in the same location as the dates
change.



A B C D
1/5/08 1/19/08
Fred Regular 500.00 625.00
Fred Overtime 100.00 125.00
Fred Gross 600.00 750.00
Fred FICA -30.00 -45.00
Tom Regular 300.00 350.00
Tom Overtime 75.00 80.00
Tom Gross 375.00 430.00
Tom FICA -23.00 -27.00

 

[T. Valko]

See this screencap:

http://img508.imageshack.us/img508/7980/lookup22ju3.jpg

--
Biff
Microsoft Excel MVP

The "Register" worksheet looks like what is below but with about 10 types
of
wages for each employee, approx 30 employees. I need to be able to pull
to a
summary pay period sheet for 1/5/08 for certain lines. I was doing it
with a
H or V Lookup and hard coding in the row or column number. Problem is,
adding a line, messes up all the rows and columns. I need it to look up
Freds Gross for 1/5/08, not necessarily in the same location as the dates
change.



A B C D
1/5/08 1/19/08
Fred Regular 500.00 625.00
Fred Overtime 100.00 125.00
Fred Gross 600.00 750.00
Fred FICA -30.00 -45.00
Tom Regular 300.00 350.00
Tom Overtime 75.00 80.00
Tom Gross 375.00 430.00
Tom FICA -23.00 -27.00

 

[Lainey]

Thank you, I typed this in but I got a #N/A, will look again after a break.
Maybe I have another error somewhere

 

[T. Valko]

If you get a #N/A error it's coming from the MATCH function which means it
can't find a matching date.

Make sure the lookup_date and the table dates are all TRUE excel dates.

 

[Lainey]

THANK YOU THANK YOU THANK YOU!!!!!
I got it, cleaned up my match problem and it worked!

Thanks so very much

 

[T. Valko]

You're welcome. Thanks for the feedback!