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?

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?

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?

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?