VLOOKUP

R

Rajhans

Hi,
Please find the excel sheet entry as below.I need to find whether the values
in coloumn A is available in the coloumn B or not.Presisaly,i need to find
out that which cell entry is not available in coloumn B.How can it be done by
VLOOKUP or other way.
Many Thanks in advance.
Raj

Coloumn A Coloumn B
A17 1110 108212V00 A10 1000 100010V00
A17 1112 108412V00 A10 1000 100010V70
A20 1100 120311V70 A10 1000 100010V90
D10 1202 203510V70 A10 1000 100011V00
H10 1300 302437V00 A10 1000 100012V00
N28 1000 464001V01 A10 1000 100012V02
N28 1000 464001V02 A10 1000 100013V00
N28 1000 464001V03 A10 1000 100013V01
N28 1000 464001V04 A10 1000 100013V02
N50 1000 450010V00 A10 1000 100014V70
N50 1100 450101V01 A10 1000 100015V70
N50 1100 450101V02 A10 1000 100016V00
N50 1100 450102V01 A10 1000 100016V70
N50 1100 450102V02 A10 1000 100017V00
N50 1100 450103V00 A10 1000 100017V70
N50 1100 450104V00 A10 1000 100310V00
N50 1100 450105V01 A10 1000 100311V00
N50 1100 450105V02 A10 1000 100312V00
N50 1100 450106V01 A10 1002 100110V00
N50 1100 450106V02 A10 1002 100110V70
N50 1100 450107V01 A10 1002 100110V90
N50 1100 450107V02 A10 1004 100210V00
N50 1100 450108V01 A10 1004 100210V70
N50 1100 450108V02 A10 1100 100510V00
N50 1100 450109V70 A10 1100 100510V70
N50 1100 450201V01 A10 1100 100510V90
N50 1100 450201V02 A10 1200 100600V00
N50 1100 450202V00 A10 1200 100600V70
N50 1100 450203V01 A10 1200 100601V00
N50 1100 450203V02 A10 1200 100601V70
N50 1100 450204V01 A10 1200 100602V00
N50 1100 450204V02 A10 1200 100602V70
N50 1100 450205V70 A10 1200 100700V00
N50 1100 450301V01 A10 1200 100700V70
N50 1100 450301V02 A10 1200 100701V00
N50 1100 450302V00 A10 1200 100701V70
N50 1100 450303V01 A10 1200 100702V00
N50 1100 450303V02 A10 1200 100702V70
N50 1100 450304V01 A10 1200 100703V00
N50 1100 450304V02 A10 1200 100703V70
N50 1100 450305V70 A11 1000 101010V00
N50 1100 450401V01 A11 1000 101010V70
N50 1100 450401V02 A11 1000 101010V90
N50 1100 450402V00 A11 1000 101011V70
N50 1100 450403V01 A11 1000 101012V00
N50 1100 450403V02 A11 1000 101012V70
N50 1100 450404V01 A11 1000 101013V00
N50 1100 450404V02 A11 1000 101013V70
N50 1100 450501V01 A11 1000 101013V80
N50 1100 450501V02 A11 1000 101013V90
N50 1100 450502V00 A11 1000 101014V00
N50 1100 450503V01 A11 1000 101014V70
N50 1100 450503V02 A11 1000 101015V70
N50 1200 451001V01 A11 1000 101016V00
N50 1200 451001V02 A11 1000 101016V70
N50 1200 451002V01 A11 1000 101310V00
N50 1200 451003V01 A11 1000 101310V70
N50 1200 451003V02 A11 1002 101110V00
N50 1200 451004V01 A11 1002 101110V70
N50 1200 451004V02 A11 1002 101110V90
N50 1200 451101V01 A11 1002 101111V00
N50 1200 451101V02 A11 1002 101111V70
N50 1200 451102V01 A11 1002 101111V80
N50 1200 451103V01 A11 1002 101112V00
N50 1200 451103V02 A11 1002 101112V70
N50 1200 451104V01 A11 1002 101112V90
N50 1200 451104V02 A11 1002 101113V70
N50 1200 451105V70 A11 1002 101113V90
N50 1200 451201V01 A11 1002 101114V70
N50 1200 451201V02 A11 1002 101115V00
N50 1200 451202V01 A11 1002 101115V70
N50 1200 451203V01 A11 1002 101116V00
N50 1200 451203V02 A11 1002 101116V70
N50 1200 451204V01 A11 1004 101210V00
N50 1200 451204V02 A11 1004 101210V70
N50 1200 451205V70 A11 1004 101211V00
N50 1200 451301V01 A11 1004 101211V70
N50 1200 451301V02 A11 1100 101510V00
N50 1200 451302V01 A11 1100 101510V70
N50 1200 451303V01 A11 1100 101511V00
N50 1200 451303V02 A11 1100 101511V70
N50 1200 451304V01 A11 1100 101512V00
N50 1200 451304V02 A11 1100 101512V70
N50 1200 451305V70 A11 1100 101513V90
N50 1200 451401V01 A11 1100 101514V00
N50 1200 451401V02 A11 1100 101514V70
N50 1200 451402V01 A11 1100 101515V00
N50 1200 451403V01 A11 1100 101515V70
N50 1200 451403V02 A11 1100 101710V00
N50 1200 451404V01 A11 1102 101610V00
N50 1200 451404V02 A11 1102 101610V70
N50 1200 451501V01 A11 1102 101611V00
N50 1200 451501V02 A11 1102 101611V70
N50 1200 451502V00 A11 1102 101612V00
N50 1200 451503V01 A11 1102 101612V70
N50 1200 451503V02 A11 1102 101613V00
N50 1200 451601V03 A11 1102 101613V70
N50 1200 451601V04 A11 1102 101614V00
N50 1200 451601V05 A11 1102 101614V70
N50 1200 451601V06 A11 1200 143000V00
N50 1200 451601V07 A11 1200 143000V70
N50 1200 451602V08 A11 1200 143100V00
N50 1200 451603V70 A11 1200 143100V70
N50 1300 452001V20 A11 1200 143101V00
N50 1300 452002V21 A11 1200 143101V70
N50 1300 452101V20 A12 1000 102010V00
N50 1300 452102V21 A12 1000 102010V70
N50 1300 452201V20 A12 1000 102010V90
N50 1300 452201V22 A12 1000 102011V00
N50 1300 452202V21 A12 1000 102011V70
N50 1300 452202V23 A12 1000 102011V90
N50 1300 452301V20 A12 1100 102110V00
N50 1300 452301V22 A12 1100 102110V70
N50 1300 452302V21 A12 1100 102210V00
N50 1300 452302V23 A12 1100 102210V70
N50 1400 453001V30 A13 1000 102510V00
N50 1400 453001V31 A13 1000 102510V70
N50 1400 453001V32 A13 1000 102511V00
N50 1400 453001V34 A13 1000 102511V70
N50 1400 453002V35 A13 1000 102512V00
N50 1400 453002V36 A13 1000 102512V70
N50 1400 453002V37 A13 1000 102513V00
N50 1400 453002V39 A13 1000 102513V70
N50 1400 453101V30 A13 1000 102514V00
N50 1400 453101V31 A13 1002 102610V00
N50 1400 453101V32 A13 1002 102610V70
N50 1400 453101V34 A13 1002 102611V00
N50 1400 453102V35 A13 1002 102611V70
N50 1400 453102V36 A13 1002 102612V00
N50 1400 453102V37 A13 1002 102612V70
N50 1400 453102V39 A13 1002 102612V90
N50 1400 453201V30 A13 1002 102613V00
N50 1400 453201V31 A13 1002 102613V70
N50 1400 453201V32 A13 1002 102614V00
N50 1400 453201V34 A13 1002 102614V70
N50 1400 453202V35 A13 1002 102615V00
N50 1400 453202V36 A13 1010 103010V00
N50 1400 453202V37 A13 1010 103010V70
N50 1400 453202V39 A13 1012 103110V00
N50 1400 453301V30 A13 1012 103110V70
N50 1400 453301V31 A13 1100 103210V00
N50 1400 453301V32 A13 1100 103210V70
N50 1400 453301V33 A13 1100 103310V00
N50 1400 453301V34 A13 1100 103310V70
N50 1400 453302V35 A13 1100 103311V00
N50 1400 453302V36 A13 1100 103311V70
N50 1400 453302V37 A14 1000 103510V00
N50 1400 453302V38 A14 1000 103510V70
N50 1400 453302V39 A14 1000 103510V90
N50 1500 454001V31 A14 1000 103511V00
N50 1500 454002V36 A14 1000 103511V70
N50 1600 454501V30 A14 1000 103511V90
N50 1600 454501V31 A14 1000 103512V00
N50 1600 454501V34 A14 1100 104010V00
N50 1600 454502V35 A14 1100 104010V70
N50 1600 454502V36 A14 1100 104010V90
N50 1600 454502V39 A14 1100 104011V00
N50 1600 454601V30 A14 1100 104011V01
N50 1600 454601V31 A14 1100 104011V02
N50 1600 454602V35 A14 1100 104011V03
N50 1600 454602V36 A14 1100 104011V70
N50 1600 454602V39 A14 1100 104011V90
N50 1600 454701V30 A14 1100 104012V00
N50 1600 454701V31 A14 1100 104012V70
N50 1600 454702V35 A14 1102 104110V00
N50 1600 454702V36 A14 1102 104110V90
N50 1600 454801V31 A15 1000 104510V00
N50 1600 454802V36 A15 1000 104510V70
N50 1700 456001V32 A15 1000 104510V90
N50 1700 456001V33 A15 1000 104511V00
N50 1700 456002V37 A15 1000 104511V10
N50 1700 456002V38 A15 1000 104511V70
N50 1700 456101V32 A15 1000 104512V00
N50 1700 456102V37 A15 1002 104610V00
N50 1700 456201V32 A15 1002 104610V70
N50 1700 456201V33 A15 1002 104611V00
N50 1700 456202V37 A15 1007 104710V70
N50 1700 456202V38 A15 1100 105010V00
N50 1700 456301V32 A15 1100 105010V70
N50 1700 456301V33 A15 1100 105010V90
N50 1700 456302V37 A15 1100 105011V70
N50 1700 456302V38 A15 1100 105012V00
N50 1700 456401V32 A15 1100 105012V70
N50 1700 456401V33 A15 1100 105012V90
N50 1700 456402V37 A15 1100 105013V00
N50 1700 456402V38 A15 1100 105013V70
N50 1700 456601V32 A15 1100 105110V11
N50 1700 456601V33 A15 1100 105110V12
N50 1700 456602V37 A15 1100 105110V19
N50 1700 456602V38 A15 1102 105210V00
N50 1800 457001V32 A15 1102 105210V70
N50 1800 457002V37 A15 1102 105211V00
N50 1800 457101V32 A15 1102 105211V70
N50 1800 457102V37 A15 1102 105211V90
N50 1800 457201V32 A15 1102 105300V00
N50 1800 457202V37 A15 1102 105300V70
N50 1800 457301V32 A15 1102 105301V00
N50 1800 457302V37 A15 1102 105301V70
N50 1900 458001V40 A16 1000 106010V70
N50 1900 458001V41 A16 1000 106110V00
N50 1900 458002V40 A16 1000 106110V70
N50 1900 458002V41 A16 1000 106110V90
N50 1900 458003V40 A16 1001 106300V00
N50 1900 458003V41 A16 1001 106300V70
N50 1900 458004V40 A16 1002 106210V00
N50 1900 458004V41 A16 1002 106210V70
N50 1900 458005V70 A16 1002 106211V00
N50 1900 458101V42 A16 1100 106510V00
N50 1900 458101V43 A16 1100 106510V01
N50 1900 458102V42 A16 1100 106510V02
N50 1900 458102V43 A16 1100 106511V00
N50 1900 458103V42 A16 1100 106511V01
N50 1900 458103V43 A16 1100 106511V02
N50 1900 458104V42 A16 1100 106511V90
N50 1900 458104V43 A16 1100 106512V00
N50 1900 458105V42 A16 1102 106610V00
N50 1900 458105V43 A16 1102 106610V70
N50 1900 458106V42 A16 1102 106611V00
N50 1900 458106V43 A16 1102 106611V70
N50 1900 458107V42 A16 1104 106710V70
N50 1900 458107V43 A16 1104 106810V12
N50 2000 458501V44 A16 1104 106810V13
N50 2000 458501V45 A16 1104 106811V00
N50 2000 458601V44 A16 1104 106811V70
N50 2000 458601V45 A16 1106 107010V70
N50 2000 458602V46 A16 1106 107010V90
N50 2000 458602V47 A17 1000 107410V70
N50 2000 458701V44 A17 1002 107510V00
N50 2000 458701V45 A17 1002 107511V00
N50 2000 458702V46 A17 1002 107511V70
N50 2000 458702V47 A17 1002 107512V00
N50 2100 459001V32 A17 1002 107512V70
N50 2100 459002V37 A17 1002 107512V90
N50 2100 459101V32 A17 1002 107513V00
N50 2100 459102V37 A17 1002 107513V70
N50 2100 459103V32 A17 1010 107610V00
N50 2100 459104V37 A17 1010 107610V69
N50 2100 459105V32 A17 1010 107610V70
N50 2100 459106V37 A17 1010 107611V00
N50 2100 459107V32 A17 1010 107611V70
N50 2100 459108V37 A17 1010 107810V00
N50 2100 459202V21 A17 1010 107810V70
N50 2100 459204V21 A17 1010 107811V00
N50 2100 459206V21 A17 1010 107811V70
N50 2100 459208V21 A17 1010 107812V00
N50 2100 459305V20 A17 1010 107812V70
N50 2100 459306V21 A17 1010 107813V00
N50 2100 459307V20 A17 1010 107813V70
N50 2100 459308V21 A17 1012 107710V00
N50 2100 459402V70 A17 1012 107710V70
N50 2200 464401V01 A17 1012 107711V00
N50 2200 464401V02 A17 1012 107711V70
N50 2200 464401V03 A17 1012 107910V00
N50 2200 464401V04 A17 1012 107910V70
N50 2200 464401V05 A17 1012 107911V00
N50 2200 464402V01 A17 1012 107911V70
N50 2200 464402V02 A17 1012 107912V00
N50 2200 464402V03 A17 1012 107912V70
N50 2200 464402V04 A17 1102 108010V00
N50 2200 464402V05 A17 1102 108010V10
N50 2200 464501V01 A17 1102 108010V70
N50 2200 464501V02 A17 1102 108011V00
N50 2200 464501V03 A17 1102 108011V10
N50 2200 464501V04 A17 1102 108011V70
N50 2200 464501V05 A17 1102 108012V00
N50 2200 464502V01 A17 1102 108012V70
N50 2200 464502V02 A17 1110 108110V00
N50 2200 464502V03 A17 1110 108110V70
N50 2200 464502V04 A17 1110 108111V00
N50 2200 464502V05 A17 1110 108111V70
N50 2200 464601V01 A17 1110 108111V90
N50 2200 464601V02 A17 1110 108112V00
N50 2200 464601V03 A17 1110 108112V70
N50 2200 464602V01 A17 1110 108112V90
N50 2200 464602V02 A17 1110 108113V00
N50 2200 464602V03 A17 1110 108113V70
N50 2200 464901V01 A17 1110 108113V90
N50 2200 464901V02 A17 1110 108114V00
N50 2200 464902V01 A17 1110 108114V70
N50 2200 464902V02 A17 1110 108114V90
N50 2200 464903V70 A17 1110 108115V00
N50 2200 465001V01 A17 1110 108115V70
N50 2200 465001V02 A17 1110 108115V90
N50 2200 465001V03 A17 1110 108116V00
N50 2200 465001V04 A17 1110 108116V70
N50 2200 465001V05 A17 1110 108116V90
N50 2200 465002V01 A17 1110 108210V00
N50 2200 465002V02 A17 1110 108210V70
N50 2200 465002V03 A17 1110 108211V00
N50 2200 465002V04 A17 1110 108211V70
N50 2200 465002V05 A17 1110 108212V00
N50 2200 465101V01 A17 1110 108212V70
N50 2200 465101V02 A17 1112 108310V00
N50 2200 465102V01 A17 1112 108310V70
N50 2200 465201V01 A17 1112 108311V00
N50 2200 465201V02 A17 1112 108311V10
N50 2200 465202V01 A17 1112 108311V70
N50 2200 465202V02 A17 1112 108312V00
N50 2200 466101V01 A17 1112 108312V70
N50 2200 466101V02 A17 1112 108313V00
N50 2200 466101V03 A17 1112 108313V10
N50 2200 466101V04 A17 1112 108313V70
N50 2200 466101V05 A17 1112 108314V00
N50 2200 466102V01 A17 1112 108314V70
N50 2200 466102V02 A17 1112 108314V90
N50 2200 466102V03 A17 1112 108315V00
N50 2200 466102V04 A17 1112 108315V70
N50 2200 466102V05 A17 1112 108315V90
N51 1000 460001V01 A17 1112 108316V00
N51 1000 460002V02 A17 1112 108316V70
N51 1000 460003V99 A17 1112 108316V90
N51 1000 460101V01 A17 1112 108317V00
N51 1000 460102V02 A17 1112 108317V70
N51 1100 460501V50 A17 1112 108317V90
N51 1100 460501V51 A17 1112 108318V00
N51 1100 460502V50 A17 1112 108318V70
N51 1100 460502V51 A17 1112 108318V90
N51 1100 460503V52 A17 1112 108319V00
N51 1100 460503V53 A17 1112 108319V70
N51 1100 460504V52 A17 1112 108320V00
N51 1100 460504V53 A17 1112 108320V70
N51 1100 460505V52 A17 1112 108321V00
N51 1100 460505V53 A17 1112 108321V70
N51 1100 460506V52 A17 1112 108410V00
N51 1100 460506V53 A17 1112 108411V00
N51 1100 460601V99 A17 1112 108412V00
N51 1100 460701V98 A17 1112 108412V70
N51 1200 461301V00 A17 1112 108413V00
N51 1200 461302V00 A17 1112 108413V70
N51 1200 461303V00 A18 1000 110010V00
N51 1200 461304V00 A18 1000 110010V70
N51 1200 461305V00 A18 1000 110010V90
N51 1200 461306V00 A18 1000 110011V00
N51 1200 461501V00 A18 1000 110011V70
N51 1300 462201V00 A18 1000 110012V00
N51 1300 462202V00 A18 1000 110012V70
N51 1300 462204V00 A18 1000 110012V90
N51 1300 462205V00 A18 1002 110110V00
N51 1300 462206V00 A18 1002 110110V70
N51 1300 462301V99 A18 1002 110110V90
N51 1500 461001V00 A18 1002 110111V00
N51 1500 461002V00 A18 1002 110112V00
N51 1500 461006V00 A18 1002 110112V70
N51 1500 461007V00 A18 1010 110210V00
N51 1500 461008V00 A18 1010 110210V70
N51 1500 461104V00 A18 1010 110211V00
N51 1500 461105V00 A18 1010 110211V70
N51 1500 461106V00 A18 1010 110212V00
N51 1500 461107V00 A18 1010 110212V70
N51 1500 461201V00 A18 1012 110310V00
N51 1500 461202V00 A18 1012 110310V70
N52 1000 465501V01 A18 1012 110311V00
N52 1000 465602V02 A18 1012 110311V70
N52 1000 465603V01 A18 1012 110312V00
N52 1000 465603V02 A18 1012 110312V70
N52 1000 465604V01 A18 1100 111010V00
N52 1000 465604V02 A18 1102 111110V00
N52 1000 465605V01 A18 1110 111210V00
N52 1000 465605V02 A18 1110 111210V70
N52 1000 465606V01 A18 1110 111212V00
N52 1000 465607V01 A18 1110 111212V70
N52 1000 465607V02 A18 1112 111310V00
N52 1000 465608V00 A18 1112 111310V70
N52 1100 465800V00 A18 1112 111312V00
R10 1000 602303V00 A18 1112 111312V70
R10 1000 602305V00 A18 1200 112010V00
R10 1001 602402V00 A18 1200 112010V70
R10 2002 603302V00 A18 1200 112011V00
R10 4004 604001V00 A18 1200 112011V70
R10 4005 604103V00 A18 1200 112012V00
R10 4005 604204V00 A18 1200 112012V70
R20 1000 350010V00 A18 1200 112110V00
R20 1000 350010V06 A18 1200 112110V70
R20 1000 350010V08 A18 1200 112110V90
R20 1000 350010V10 A18 1200 112210V00
R20 1000 350010V20 A18 1200 112210V70
R20 1000 350020V00 A18 1202 112310V00
R20 1000 350020V06 A18 1202 112310V70
R20 1000 350030V00 A18 1202 112311V00
R20 1000 350060V00 A18 1202 112311V70
R20 1000 350210V50 A18 1202 112312V00
R20 1000 350212V00 A18 1202 112312V70
R20 1000 350212V90 A18 1202 112410V00
R20 1000 350214V00 A18 1202 112410V70
R20 1000 350216V00 A18 1202 112510V00
R20 1000 350218V00 A18 1202 112510V70
R20 1000 350220V00 A18 1202 112510V90
R20 1000 350222V00 A18 1202 113510V70
R20 1000 350224V00 A18 1210 112610V00
R20 1000 350260V70 A18 1210 112611V00
R20 1000 350410V00 A18 1210 112611V70
R20 1000 350412V00 A18 1210 112612V00
R20 1000 350414V00 A18 1210 112612V70
R20 1000 350416V00 A18 1210 112612V90
R20 1000 350420V00 A18 1210 112613V70
R20 1000 350422V00 A18 1212 113010V00
R20 1000 350460V70 A18 1212 113011V00
R20 1000 350610V00 A18 1212 113011V70
R20 1000 350612V00 A18 1212 113012V00
R20 1000 350810V00 A18 1212 113012V70
R20 1000 350812V00 A18 1212 113012V90
R20 1000 350816V00 A18 1213 113110V00
R20 1000 351010V50 A18 1213 113110V70
R20 1000 351014V00 A18 1213 113210V00
R20 1000 351016V00 A18 1213 113210V70
R20 1000 351018V51 A19 1000 114010V00
R20 1000 351020V51 A19 1000 114010V70
R20 1000 351020V52 A19 1000 114010V90
R20 1000 351050V00 A19 1000 114110V00
R20 1000 351051V00 A19 1000 114110V02
R20 1000 351052V00 A19 1000 114111V00
R20 1000 351054V00 A19 1000 114112V00
R20 1000 351056V00 A19 1000 114113V00
R20 1000 351056V50 A19 1000 114113V02
R20 1000 351057V00 A19 1000 114113V90
R20 1000 351210V00 A19 1000 114114V00
R20 1000 351210V50 A19 1000 114115V00
R20 1000 351212V00 A19 1000 114410V00
R20 1000 351260V70 A19 1002 114210V07
R20 1000 351410V00 A19 1002 114210V08
R20 1000 351412V00 A19 1002 114210V09
R20 1000 351414V00 A19 1002 114210V70
R20 1000 351416V00 A19 1002 114211V70
R20 1000 351610V00 A19 1004 114310V00
R20 1000 351612V00 A19 1004 114310V70
R20 1000 351620V00 A19 1004 114311V00
R20 1000 351620V02 A19 1004 114312V00
R20 1000 351660V70 A19 1100 115010V00
R20 1000 351810V00 A19 1100 115110V00
R20 1000 351812V00 A19 1100 115110V70
R20 1000 351850V00 A19 1100 115110V90
R20 1000 355010V70 A19 1100 115111V00
V40 0000 834018V00 A19 1100 115111V70
Z10 1000 900020V00 A19 1200 116010V70
ZZZ 1000 999920 A19 1200 116011V70
 
P

Pete_UK

You could use conditional formatting to highlight a cell in column A
if it is not contained anywhere in column B - is that what you want?
Alternatively, you could have a formula in column C which is copied
down and would return, say, "yes" if the cell does have a match and
"no" if it doesn't, so that you could then apply autofilter to column
C to select either the Yes or the No cells.

Or you could do both.

Are any of these approaches what you want to do?

Pete
 
R

Rajhans

Ya Pete.Ur right.I wanted the same thing.But how to put conditional formatting.
Regards
Raj
 
S

Spiky

Ya Pete.Ur right.I wanted the same thing.But how to put conditional formatting.
Regards
Raj

Go to A1 and do conditional formatting. Pick your highlight format.
Enter this formula for the condition: =OR(A1=$B$1:$B$1000)=FALSE

Adjust the "$B$1000" part of the formula for the proper length of your
data. Then use copy/paste formats or the Format Painter to copy just
the format down all the data in Column A.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top