vlookup returning a n/a result

[jeanette.rimmer]

hi, Im receiving daily data in the form of a list of names and the hours
worked at several different pay rates.
I need to merge this daily info to provide a report of hours worked at which
rate

I have set up a vlookup which checks for the employee name on my full list
of employees and shows hrs worked at eg Rate 1, 2 for each day. I will then
summarise this on another sheet.

But Im getting lots of N/A results when an employee hasnt worked on a
particular day this then means I cant create a summary.
Is there a way of getting rid of N/A results or am I going the wrong way
about this?


Thanks in advance

 

[JE McGimpsey]

one way:

Assume your VLOOKUP is

=VLOOKUP(A1,J:K,2,FALSE)


Then use

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

or, alternatively:

=IF(COUNTIF(J:J,A1),VLOOKUP(A1,J:K,2,FALSE),"")

then you can use SUM() which ignores the null string text ("").

 

[Anne Troy]

You could try something like this, Jeanette:
=if(isna(vlookup...),0,vlookup...)
*******************
~Anne Troy

www.OfficeArticles.com

 

[jeanette.rimmer]

hi, I used this formula

=IF(ISNA(VLOOKUP($B3,HrsMon,2,FALSE)),"",VLOOKUP($B3,HrsMon,2,FALSE))
as suggested

It gets rid of the #N/a as required but also gets rid of my valid results?

Any ideas?


Thanks

 

[jeanette.rimmer]

Thanks Anne, and JE Ive sorted it out, my own daft mistake

jeanette