cross referencing

[JockW]

Hi guys,
what would be the best way to cross reference a list of names in workbook1
with a huge list of names in workbook2. Ideally, if a name from w/b1 isn't
found in w/b2, then "Y" to appear next to the name in w/b1.
Any ideas?

 

[Fred Smith]

Use Vlookup.

With both workbooks open, in your target cell, type:

=Vlookup(

Now click the cell in wb1 with the name you want to look up.
Type a comma
Click on the Range in wb2 that has the names.
Press F4
Then finish off with: ,1,false)

The advantage of this method is Excel will fill in the cell addresses for
you. You don't risk typing errors.

Now you need to make the change to support what happens when Excel can't
find the entry in wb2. To do this, change your formula to something like:

=IF(ISNA(yourformula),"Y",yourformula)

Copy down as needed.

Regards,
Fred.

 

[T. Valko]

In workbook1 names on sheet1 in the range A1:An

Workbook2 names on sheet1 in the range A1:A20

Enter this formula on Sheet1 workbook1 cell B1:

=IF(ISNA(MATCH(A1,'[workbook1.xls]Sheet1'!A$1:A$20,0)),"Y","")

Copy down as needed

 

[JockW]

Thanks to both
--
tia

In workbook1 names on sheet1 in the range A1:An

Workbook2 names on sheet1 in the range A1:A20

Enter this formula on Sheet1 workbook1 cell B1:

=IF(ISNA(MATCH(A1,'[workbook1.xls]Sheet1'!A$1:A$20,0)),"Y","")

Copy down as needed

--
Biff
Microsoft Excel MVP

Hi guys,
what would be the best way to cross reference a list of names in workbook1
with a huge list of names in workbook2. Ideally, if a name from w/b1 isn't
found in w/b2, then "Y" to appear next to the name in w/b1.
Any ideas?

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks to both
--
tia

In workbook1 names on sheet1 in the range A1:An

Workbook2 names on sheet1 in the range A1:A20

Enter this formula on Sheet1 workbook1 cell B1:

=IF(ISNA(MATCH(A1,'[workbook1.xls]Sheet1'!A$1:A$20,0)),"Y","")

Copy down as needed

--
Biff
Microsoft Excel MVP

Hi guys,
what would be the best way to cross reference a list of names in
workbook1
with a huge list of names in workbook2. Ideally, if a name from w/b1
isn't
found in w/b2, then "Y" to appear next to the name in w/b1.
Any ideas?