Excel FIND/SEARCH Find Name Hours Worked

**staceynix** · 01-20-2013, 04:12 AM

I need a formula or series of formulas. I want excel to find a persons name and then figure out which cell its in then show the hours worked in an adjacent cell. Example I have tabs for Monday through Friday. On the monday tab I want it to search A1:A30 for a persons name. So if A1 equals John Smith then I want it to look at the info in D1 and show that data. Oh and the data is time, to make it more difficult. I am trying to make it figure out a persons time worked for the week. I have tried different FIND/SEARCH formulas but am not getting what I want.

**zbor** · 01-20-2013, 04:21 AM

Can you upload example workbook?

**Pauleyb** · 01-21-2013, 02:04 PM

Look at VLOOKUP. It sounds like it should do what you want.

**staceynix** · 01-31-2013, 02:05 AM

The VLOOKUP does exactly what i want. However if the person it searches for is not listed it gives me an N/A. Is there a way to make it say 0? Thank You!

**FDibbins** · 01-31-2013, 02:12 AM

Your profile says 2003, so use this...

=if(iserror(your-vlookup),0,your-vlookup)

If you have 2007 or later, please update your profile, and then you can use...

=iferror(your-vlookup,0)

**staceynix** · 01-31-2013, 02:53 AM

Next Issue, using =LOOKUP("John Smith",'Monday-D'!C7:C37,'Monday-D'!J7:J37) This gives me the correct total it doesn't matter if John Smith is on C9 or C15 etc. But on the next cell/line if I use =LOOKUP("Jane Doe",'Monday-D!C7:C37,'Monday-D'J7:J37) it only gives me the same result as John Smith. It doesn't reference the new name. I don't understand why the formula would do this?

**FDibbins** · 01-31-2013, 03:10 AM

why not just use a regular vlookup?

**staceynix** · 01-31-2013, 03:57 AM

Ok so I am now trying =VLOOKUP(A5,'Monday-D'!C7:C37,'Monday-D'!J7:J37,FALSE) I keep getting #REF! error. It says Moving or deleting cells caused and invalid cell reference, or function is returning reference error.

**FDibbins** · 02-01-2013, 01:39 AM

vlookup syntax is...

=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

you are using a range ('Monday-D'!J7:J37) when it is asking for a column number