Using Multiple IF Statements

[ddhawks]

Hi Everyone!

I'm working on a spreadsheet where everyone's work hours are tied to the project he or she is working on. For example, I have the name of the person in Column D, and Column R is the hours billed to the job. The hours billed to the job comes from a list at the bottom of the spreadsheet which is linked to another spreadsheet and is updated monthly. This data is found in Column B, Rows 40-59, with each row the number of hours each person worked.

Here is the formula I'm using: =IF(D4="Sean Mahoney",B44,IF(D4="Brad Stabenow",B45,IF(D4="Nicole Anderson",B41,IF(D4="Matt Huderski",B43,IF(D4="Shea Groom",B42,IF(D4="James Sherry",B52))))))

The very first row under PM Hours Billed to Jobs is correct. None of the others are. Is there a better formula to use? I copied and pasted my spreadsheet below because I couldn't figure out to insert a link. If anyone can help me out, I'd greatly appreciate it.

Thank you!
Darleen


[TABLE="width: 760"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]Closed[/TD]
[TD]Job Name[/TD]
[TD]Project Manager[/TD]
[TD]Superintendent[/TD]
[TD]PM Hours Billed To Jobs[/TD]
[TD][/TD]
[TD]Supt Hours Billed To Jobs[/TD]
[/TR]
[TR]
[TD]2016-474[/TD]
[TD][/TD]
[TD]RMR US Treasury TI[/TD]
[TD]Sean Mahoney[/TD]
[TD]James Martin[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD]2016-495[/TD]
[TD][/TD]
[TD]CRMC Dialysis Addition[/TD]
[TD]Brad Stabenow[/TD]
[TD]Dan Ozborn[/TD]
[TD="align: right"]7.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12.00[/TD]
[/TR]
[TR]
[TD]2016-496[/TD]
[TD][/TD]
[TD]Swope OBGYN & Pediatric[/TD]
[TD]Nicole Anderson[/TD]
[TD]Troy McKitrick[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]2017-497[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sean Mahoney[/TD]
[TD]Derrick Shanks[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17.00[/TD]
[/TR]
[TR]
[TD]2017-509[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt Huderski[/TD]
[TD]James Sherry[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13.00[/TD]
[/TR]
[TR]
[TD]2017-511[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt Huderski[/TD]
[TD]Troy McKitrick[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]2017-513[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shea Groom[/TD]
[TD]John Alexander[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8.00[/TD]
[/TR]
[TR]
[TD]2017-518[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nicole Anderson[/TD]
[TD]James Sherry[/TD]
[TD="align: right"]9.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13.00[/TD]
[/TR]
[TR]
[TD]2017-520[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sean Mahoney[/TD]
[TD]Jake Lawson[/TD]
[TD="align: right"]13.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15.00[/TD]
[/TR]
[TR]
[TD]2017-523[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sean Mahoney[/TD]
[TD]James Martin[/TD]
[TD="align: right"]14.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD]2017-527[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nicole Anderson[/TD]
[TD]Mitch Welty[/TD]
[TD="align: right"]12.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14.00[/TD]
[/TR]
[TR]
[TD]2017-529[/TD]
[TD][/TD]
[TD][/TD]
[TD]Brad Stabenow[/TD]
[TD]Gus Purdum[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20.00[/TD]
[/TR]
[TR]
[TD]2017-531[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt Huderski[/TD]
[TD]Aaron Hankammer[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.00[/TD]
[/TR]
[TR]
[TD]2017-532[/TD]
[TD][/TD]
[TD][/TD]
[TD]James Sherry[/TD]
[TD]James Sherry[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13.00[/TD]
[/TR]
[TR]
[TD]2017-533[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shea Groom[/TD]
[TD]John Alexander[/TD]
[TD="align: right"]17.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8.00[/TD]
[/TR]
[TR]
[TD]2017-534[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nicole Anderson[/TD]
[TD][/TD]
[TD="align: right"]17.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-535[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sean Mahoney[/TD]
[TD]James Martin[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10.00[/TD]
[/TR]
[TR]
[TD]2017-536[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt Huderski[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-537[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shea Groom[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-538[/TD]
[TD][/TD]
[TD][/TD]
[TD]Brad Stabenow[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-539[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt Huderski[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2017-540[/TD]
[TD][/TD]
[TD][/TD]
[TD]James Sherry[/TD]
[TD]James Sherry[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13.00[/TD]
[/TR]
[TR]
[TD]2017-541[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shea Groom[/TD]
[TD]Aaron Hankammer[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.00[/TD]
[/TR]
[TR]
[TD]2017-542[/TD]
[TD][/TD]
[TD][/TD]
[TD]Shea Groom[/TD]
[TD]Aaron Hankammer[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.00[/TD]
[/TR]
[TR]
[TD]2017-543[/TD]
[TD][/TD]
[TD][/TD]
[TD]Matt Huderski[/TD]
[TD]Troy McKitrick[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11.00[/TD]
[/TR]
[TR]
[TD]2017-544[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nicole Anderson[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2018-545[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nicole Anderson[/TD]
[TD]James Sherry[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]13.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs to Jobs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Don[/TD]
[TD]1.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nicole[/TD]
[TD]2.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shea[/TD]
[TD]3.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt[/TD]
[TD]4.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]5.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brad[/TD]
[TD]6.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aaron[/TD]
[TD]7.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]8.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]9.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James M[/TD]
[TD]10.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Troy[/TD]
[TD]11.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dan[/TD]
[TD]12.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James S[/TD]
[TD]13.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mitch[/TD]
[TD]14.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jacob L[/TD]
[TD]15.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jeffrey M[/TD]
[TD]16.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Derrick[/TD]
[TD]17.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jacob A[/TD]
[TD]18.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jeff P[/TD]
[TD]19.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gus[/TD]
[TD]20.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Simon4s]

You would use a Vlookup
=vlookup("NAME",TABLE,column number that you wish to return,0)

 

[ddhawks]

You would use a Vlookup
=vlookup("NAME",TABLE,column number that you wish to return,0)

Simons4s,
Can you please explain this a little more? I'm not familiar with Vlookup.

Thank you!

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
3	Job #	Closed	Job Name	Project Manager	Superintendent	PM Hours Billed To Jobs		Supt Hours Billed To Jobs
4	2016-474		RMR US Treasury TI	Sean Mahoney	James Martin	5		10
4b

Cell Formulas
Range	Formula
F4	=VLOOKUP(D4,A33:B52,2,0)
H4	=VLOOKUP(E4,A33:B52,2,0)

N.B. The names must be consistent i.e. The list with the hours would have to include both first and last names.

 

[ddhawks]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=VLOOKUP(D4,A33:B52,2,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=VLOOKUP(E4,A33:B52,2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



N.B. The names must be consistent i.e. The list with the hours would have to include both first and last names.[/QUOTE]

This is EXACTLY what I needed. Thank you so much for your help!