Find, maybe

[chadlaw32]

I need to look up the sup total or Over time based on the name. Example i want to put Doe, John in a cell and have it bring me 0.77, any ideas?

Pay Date

Pay Code

Hours

Dollars

Doe

Jane

YG6001144

Sun 03/31/2019

REGULAR-Regular

0.27

XXXX

Mon 04/01/2019

REGULAR-Regular

7.37

XXXX

Tue 04/02/2019

REGULAR-Regular

8.28

XXXX

Wed 04/03/2019

REGULAR-Regular

7.83

XXXX

Thu 04/04/2019

VACATION-VACATION

8

XXXX

Fri 04/05/2019

REGULAR-Regular

4.87

XXXX

Subtotal

36.62

XXXX

Doe

John

YG6001457

Mon 04/01/2019

REGULAR-Regular

7.05

XXXX

Tue 04/02/2019

REGULAR-Regular

7.65

XXXX

Wed 04/03/2019

REGULAR-Regular

8.37

XXXX

Thu 04/04/2019

REGULAR-Regular

9.35

XXXX

Fri 04/05/2019

OVERTIME-Overtime

0.77

XXXX

Fri 04/05/2019

REGULAR-Regular

7.58

XXXX

Subtotal

40.77

XXXX

<colgroup><col><col span="4"><col><col><col span="3"><col><col span="3"><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[DanteAmor]

Try this

	A	B	C	D	E	F	G	H
1	Pay Date	Pay Code		Hours	Dollars		Name	OVERTIME-Overtime
2	Doe	David	YG6001144				Doe, John	11.14
3	Sun 03/31/2019	REGULAR-Regular		0.27	XXXX
4	Mon 04/01/2019	REGULAR-Regular		7.37	XXXX
5	Tue 04/02/2019	OVERTIME-Overtime		8.28	XXXX
6	Wed 04/03/2019	REGULAR-Regular		7.83	XXXX
7	Thu 04/04/2019	OVERTIME-Overtime		8	XXXX
8	Fri 04/05/2019	REGULAR-Regular		4.87	XXXX
9			Subtotal	36.62	XXXX
10	Doe	John	YG6001457
11	Mon 04/01/2019	REGULAR-Regular		7.05	XXXX
12	Tue 04/02/2019	REGULAR-Regular		7.65	XXXX
13	Wed 04/03/2019	OVERTIME-Overtime		8.37	XXXX
14	Thu 04/04/2019	REGULAR-Regular		9.35	XXXX
15	Fri 04/05/2019	OVERTIME-Overtime		2.77	XXXX
16	Fri 04/05/2019	REGULAR-Regular		7.58	XXXX
17			Subtotal	40.77	XXXX

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:101.7px;"><col style="width:137.82px;"><col style="width:67.49px;"><col style="width:39.92px;"><col style="width:41.82px;"><col style="width:35.17px;"><col style="width:76.04px;"><col style="width:129.27px;"></colgroup><tbody>
</tbody>

Cell Formula H2 {=SUMIF(OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)),H1,OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)))} <tbody> </tbody>

<tbody>
</tbody>

That's exactly what he does in my example

 

[chadlaw32]

It does but it also populates the answer from the first cell in any following cells with a blank cell reference.

 

[DanteAmor]

Then you could give the example of the result that the formula puts, even better, you could upload your file and show me with examples.

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[chadlaw32]

Here is an example all the cells below Sarah have no name so it just pulls the same answer for whatever name is in the first cell in this case Doe, John below is the formula I am using

=IFERROR(SUMIF(OFFSET($F$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0)-1,0,MATCH("Subtotal",OFFSET($K$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0),0,17),0)),$U$1,OFFSET($O$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0)-1,0,MATCH("Subtotal",OFFSET($K$1,MATCH(SUBSTITUTE(T2,", ",""),$A:$A&$F:$F,0),0,17),0))),"")

Employee Daily Totals Report

Support

OVERTIME-Overtime

Date Range: 03/31/2019 - 04/06/2019 and Employee Effective As Of : 04/09/2019

Doe, John

0.77

Company Code:

Brown, Sarah

0

Last Name

First Name

Position ID

0.77

Pay Date

Pay Code

Hours

Dollars

0.77

0.77

Doe

John

0.77

##########

REGULAR-Regular

7.48

XXXX

0.77

##########

REGULAR-Regular

7.47

XXXX

0.77

##########

REGULAR-Regular

7.82

XXXX

0.77

##########

OVERTIME-Overtime

0.77

XXXX

0.77

##########

REGULAR-Regular

8.10

XXXX

0.77

##########

LWOP-Leave Without Pay

8.00

XXXX

0.77

Subtotal

46.99

XXXX

0.77

Total

9.24

Brown

Sarah

##########

REGULAR-Regular

0.27

XXXX

OVERTIME-Overtime

##########

REGULAR-Regular

7.37

XXXX

0.77

##########

REGULAR-Regular

8.28

XXXX

0.77

##########

REGULAR-Regular

7.83

XXXX

0.77

##########

VACATION-VACATION

8.00

XXXX

0.77

##########

REGULAR-Regular

4.87

XXXX

0.77

Subtotal

36.62

XXXX

Total

3.85

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[DanteAmor]

But you modified the formula, that's not the formula that I gave you.

=SUMIF(OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)),H1,OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)))

Change 17 by the number of last row.

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[chadlaw32]

i had to adjust the column for the sheet I am using and I did a CTRL SHIFT ENTER to get the {} around it in the sheet. It pulls the correct info if i have a name in the reference cell but if that cell is empty to allow for adding names later it pulls the data for the first name on the list.

 

[DanteAmor]

But the idea is to put a name.
If the cell is blank then it should return blank?

Try:

=IF(G2="","",SUMIF(OFFSET(B1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0)),H1,OFFSET(D1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0)-1,0,MATCH("Subtotal",OFFSET(C1,MATCH(SUBSTITUTE(G2,", ",""),A1:A17&B1:B17,0),0,17),0))))

 

[chadlaw32]

That worked like a champ thank you for all your help you are awesome!!