Excel vlookup
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 |
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 "kiran" wrote: 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 |
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kiran" wrote in message ... 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 |
I think you have better powers of perception than me!
"Bernard Liengme" wrote: 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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kiran" wrote in message ... 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 |
All times are GMT +1. The time now is 08:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com