Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
vlookup and filename returning same result on each sheet. | Excel Discussion (Misc queries) | |||
Conversion | Excel Worksheet Functions | |||
Spellnumber | Excel Worksheet Functions |