CAMPARING 2 LISTS ( VLOOKUP OR any other function?)

**ammy.rocks** · 11-23-2013, 10:46 AM

Dear geeks ,

i got stuck as i have 3 lists

A B C

000030981658 18552042 34649236
24767D77A44C 18590570 34649228
000030948053 18860239 34567917
000030447593 18860346 34567511
30927776 18860445 34567800
24767D76F461 18860627 34567990
000031182249 18860296 34568014
34BDFA59BE95 18860528 34567982
24767D78B61E 18860569 34568030
34BDFA57D963 18860486 34567529
000029545894 18589606 34651893
24767D76D03F 22086011 36955573
24767D731C92 22089031 36959013

now I WANT TO CHECK whether the no. which is in list a exist in list b or not problem is as we see the first no. has 0000which is not helping during comparing i want to remove those first then want to compare and check with a desired result in a saperate row .

**Richard Buttrey** · 11-23-2013, 10:55 AM

It looks like your column A cells are all text. In which case you'll need to convert them before comparing to column B. So use something like

Formula:

Please Login or Register  to view this content.

in say D1 and copy this down first. Then use a simple =MATCH(D1,B:B,false) and copy this down column D. Where the result is NOT #N/A there is a match.

**rcm** · 11-23-2013, 11:02 AM

Hi,

Instead of removing pad the rest with '0's so all have the same lenght (I beleive that 11 characters is the right length for these entries)

Once that is done, compare each item against the two other list via the match() function

Below a sample of your data with some additional entries to show matching hits.