Vlookup

[jo15765]

I have a spreadsheet with account numbers in it, and then I have an "updated" spreadsheet with account numbers in it. Is it possible to use the Vlookup to compare these two spreadsheets (but only the column on Spreadsheet1 and Spreadsheet2 that contain the accounts) and find if there are duplicate account numbers?

F.Y.I. --- I found a way to do it, I just want to know if there is a way to use Vlookup to do it. If you can not do it using Vlookup, I am interested in other options, such as a VBA code also.

[noexcel=badwork]

try this code:

=IF(ISNA(VLOOKUP(Sheet1!A:A,Sheet2!$A$1:$B$5,2,FALSE)),"","Duplicate")

supposing your data in sheet1&2

[jo15765]

try this code:

=IF(ISNA(VLOOKUP(Sheet1!A:A,Sheet2!$A$1:$B$5,2,FALSE)),"","Duplicate")

Just so I am understanding this correctly, the Sheet1 is the sheet where the data is, the A:A is saying to look in all rows of column A?? then again Sheet2 is the sheet where the data I want to compare is, and the $A$1:$B$5$ is the locations in Sheet2 that I am wanting to Compare. What are the 2, False and ""? And I am assuming that "Duplicate" is there because if they are duplicates this is the word that will populate?

[noexcel=badwork]

yes, for 2 it stands for column number in sheet2 (in our example it is empty)
as for false: it compares entries and bring back the exact match
if it is true it will bring the approximate match
finally, (""), instead of #N/A coming, by this way, the cell will be empty
if conditions not applied.

[jo15765]

yes, for 2 it stands for column number in sheet2 (in our example it is empty)

And column 2, we mean "B" correct?

finally, (""), instead of #N/A coming, by this way, the cell will be empty
if conditions not applied.

Could I change what is between the "", and make it read more like

=IF(ISNA(VLOOKUP(Sheet1!A:A,Sheet2!$A$1:$B$5,2,FALSE)),"No","Yes")

And this will have Excel display, No if it is not a duplicate, and Yes if it is a duplicate?

[noexcel=badwork]

sorry if I could not explain well
but here I attached a sample of what
you need
I changed it to show "no" or "yes

[arthurbr]

Another simpler and probably more efficient approach using lesser functions In B1 enter

=IF(COUNTIF(Sheet2!$A$1:$A$5,Sheet1!A1),"yes","no")

and pull down as neeeded