Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
jeanette.rimmer
 
Posts: n/a
Default vlookup returning a n/a result

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


  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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 ("").


In article ,
"jeanette.rimmer" wrote:

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

  #3   Report Post  
Anne Troy
 
Posts: n/a
Default

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

www.OfficeArticles.com


"jeanette.rimmer" wrote in message
...
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




  #4   Report Post  
jeanette.rimmer
 
Posts: n/a
Default

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
"JE McGimpsey" wrote in message
...
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 ("").


In article ,
"jeanette.rimmer" wrote:

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



  #5   Report Post  
jeanette.rimmer
 
Posts: n/a
Default

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

jeanette
"jeanette.rimmer" wrote in message
...
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
"JE McGimpsey" wrote in message
...
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 ("").


In article ,
"jeanette.rimmer" wrote:

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I NEED HELP with the SPELLNUMBER Function vag Excel Worksheet Functions 0 June 21st 05 08:17 AM
EXCEL:NUMBER TO GREEK WORDS vag Excel Worksheet Functions 1 June 15th 05 05:57 PM
vlookup and filename returning same result on each sheet. RogueSwan Excel Discussion (Misc queries) 3 March 22nd 05 11:08 PM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
Spellnumber Norman Jones Excel Worksheet Functions 6 December 13th 04 08:21 AM


All times are GMT +1. The time now is 10:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"