Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
I have two worksheets: the main worksheet has all the employees with the
employee number in column A sorted ascending and years of service in col D. The workbrain worksheet has employee numbers in A and my formula in G which is: vlookup(a2,main!a2:d1491,4) the information is correct that is returned, but I have employees that is on the workbrain sheet and not on the main, and it is pulling in 10.75 on these 10 cells. Is there a way to add to my formula that if it doesn't find that emp # to bring in a 0? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Pammy,
Try this =IF(COUNTIF(Main!A2:A1491,A2)=0,"Lookup Not Found",VLOOKUP(A2,Main!A2:D1491,4,FALSE)) or to return Null =IF(COUNTIF(Main!A2:A1491,A2)=0,"",VLOOKUP(A2,Main !A2:D1491,4,FALSE)) Mike "Pammy" wrote: I have two worksheets: the main worksheet has all the employees with the employee number in column A sorted ascending and years of service in col D. The workbrain worksheet has employee numbers in A and my formula in G which is: vlookup(a2,main!a2:d1491,4) the information is correct that is returned, but I have employees that is on the workbrain sheet and not on the main, and it is pulling in 10.75 on these 10 cells. Is there a way to add to my formula that if it doesn't find that emp # to bring in a 0? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
Yes, change your formula as follows:
=IFERROR(VLOOKUP((a2,main!a2:d1491,4,FALSE),0) "Pammy" wrote: I have two worksheets: the main worksheet has all the employees with the employee number in column A sorted ascending and years of service in col D. The workbrain worksheet has employee numbers in A and my formula in G which is: vlookup(a2,main!a2:d1491,4) the information is correct that is returned, but I have employees that is on the workbrain sheet and not on the main, and it is pulling in 10.75 on these 10 cells. Is there a way to add to my formula that if it doesn't find that emp # to bring in a 0? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup
vlookup(a2,main!a2:d1491,4)
Try using the 4th argument and setting it to either 0 or FALSE; =VLOOKUP(A2,main!A$2:D$1491,4,0) =VLOOKUP(A2,main!A$2:D$1491,4,FALSE) Another possible method: =SUMIF(main!A$2:A$1491,A2,main!D$2:D$1491) -- Biff Microsoft Excel MVP "Pammy" wrote in message ... I have two worksheets: the main worksheet has all the employees with the employee number in column A sorted ascending and years of service in col D. The workbrain worksheet has employee numbers in A and my formula in G which is: vlookup(a2,main!a2:d1491,4) the information is correct that is returned, but I have employees that is on the workbrain sheet and not on the main, and it is pulling in 10.75 on these 10 cells. Is there a way to add to my formula that if it doesn't find that emp # to bring in a 0? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |