cross referencing

  • Thread starter Thread starter JockW
  • Start date Start date
J

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?
 
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.
 
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
 
Thanks to both
--
tia


T. Valko said:
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


JockW said:
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?
Click to expand...
Click to expand...
 
You're welcome!

--
Biff
Microsoft Excel MVP


JockW said:
Thanks to both
--
tia


T. Valko said:
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


JockW said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top