how to get such results using excel formula

i have two excel sheets

An example of the two sheets are below

sheet1

a 1
b 2
d 1
e 3
g 1

sheet2

a
b
c
d
e
f

i want to put a formula in b1 of sheet 2 and drag it down so that the resulting sheet 2 is

sheet2

a  1
b  2
c  0
d  1
e  3
f  0

expla开发者_JAVA百科nation : - a = 1 because same value in book1
                b = 2 because same value in book1
                c = 0 because c does not exist in book1
                d = 1 because same value in book1
                e = 3 because same value in book1
                f = 0 because f does not exist in book1

what formula can i use in b column of sheet 2 ?

A combination of if(), iserror() and vlookup() will be your best bet here.

Assuming your data from sheet1 is in a range called 'refdata',

=IF(ISERROR(VLOOKUP(A1,refdata,2,FALSE)),0,VLOOKUP(A1,refdata,2,FALSE))

should do what you need (Where A1 is the cell containing the data you want to match on)

In Excel 2007 it looks like this:

=IFERROR(VLOOKUP(A1,sheet1data,2,0),)