asked on

Excel Vlookup for finding the difference

wanted to compare difference in two columns in excel. I want to know which values in order2 column which are not there in order1 column.
so value ABL00004 in order2 in the below example is not there in order1 column.so the vlookup or anyother formula shoul give the value like N/A for example for this record

Order1        Order2
ABL00001      ABL00001
ABL00002      ABL00002
ABL00003 ABL00003
ABL00007      ABL00004

Many thanks

Alan

Hi,

Since you don't say, I will assume your data is in A1:B5.

Easiest option might be to enter this formula in C2 and copy down:

=ISERR(MATCH(B2, A$2:A$5,0))

The items that show TRUE are missing.

Hope that helps,

Alan.

gvamsimba

ASKER

Hi Alan, I tested the above sample data in question with your formula but its wrongly giving everything as False.Can you please check the formula once?

Order1        Order2
ABL00001      ABL00001            FALSE
ABL00002      ABL00002            FALSE
ABL00003 ABL00003            FALSE
ABL00007      ABL00004            FALSE

Thanks

ASKER CERTIFIED SOLUTION

Alan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gvamsimba

ASKER

Hi Alan, it still does not work.

Order1        Order2
ABL00001      ABL00001            TRUE
ABL00002      ABL00002            TRUE
ABL00003 ABL00003            TRUE
ABL00007      ABL00004            TRUE

Alan

Seems to work for me - see attached.

Alan.
EE-29087623-Version1.xlsx

gvamsimba

ASKER

Hi Alan, its not working for me. could this be because I am using excel 2003 version ?

=ISNA(MATCH(B2, A$2:A$5,0))

Alan

I guess its possible.

I don't have access to Excel 2003 to test that, but I found Excel 2010, and have saved the file back as XLS version and attached here.

The formulae are the same. I seem to recall ISNA and MATCH being around in Excel 97, but maybe I am wrong.

Does this one work?

Alan.
EE-29087623-Version2.xls

SOLUTION