Excel vlookup

[Guest]

Hi All,

I have a values as follows
B C D
f1cagprp.p #N/A yfrcpent.p
f1caiq.p yfrcptmt.p yfrcptmt.p
f1camt.p yfrtvmt.p yfrtvmt.p
f1cancmt.p yfstmmrp.p yfstmmrp.p
f1capmta.i yfstnen.p yfstnen.p
f1captrp.p z xageanl.p zxageanl.p
f1catgiq.p zxconwip.p zxconwip.p
f1catgmt.p zxrejrep.p zxrejrep.p
f1cdlprp.i zxvndanl.p zxvndanl.p
f1cdlprp.p #N/A
f1chklst.p #N/A
f1cholot.p #N/A
f1cimdes.p #N/A
f1cimilt.p #N/A
f1cpcomt.p #N/A
i have applied =VLOOKUP(D1,B1:B569,1,FALSE)
I want my out put to be if value in B column exists in D column i should get
1 or 0 can any one help me out....


TIA

 

[Guest]

Try either of the following
=IF(ISNA(C2),0,IF(C2=D2,1,0)) or else =IF(ISNA(C2),"",IF(C2=D2,1,0)) in cell
E2. Now copy down as far as you want to go

HTH

 

[Bernard Liengme]

In E1 (say) you want 1 if D1's value is found in B1:B569, otherwise 0
Use =MIN(1,COUNTIF($B$1:$B$569,D1))
Copy down to E2, E3, etc
best wishes

 

[Guest]

I think you have better powers of perception than me!