# EXCEL Question - Compare Data across two files, and if found return with a '1'

Discussion in 'Business Applications' started by fisher1711, Aug 23, 2012.

Not open for further replies.

Joined:
Aug 23, 2012
Messages:
1
Hi there -

Here's the situation:

• I have TWO excel files
• 'FILE A' ... Has a full list of over 2,000 entries, each with a unique ID in the far-left column ('A')
• 'FILE B' ... Has a list of 400 entries with their respective IDs (that are found within the 2,000)
• I need to compare the data found in File B with that in File A. If they appear in both files, I want a '1' to appear in the cell next to it

Here's what I need:

In Column 'B' of "FILE A", I need a formula that will do the following:
=If A2 is found in the first column in "FILE B", then show '1', if not show nothing

Does anyone have a solution? I am not certain if a VLOOKUP is the possible solution, and if so, I cannot figure out the correct formula.

Thanks again,

fisher1711

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,952
First Name:
Wayne
should be and then use a IF statement - to test if the vlookup is true or not

something like
=VLOOKUP(A2,[fileB.xlsx]Sheet1!\$A\$2:\$A\$xxxxx,1,false)
where xxxxx is the end of the row range
will do the lookup

use ISERROR to test if true of false

and then an IF to add the 1

=IF(ISERROR(VLOOKUP(A2,[fileB.xlsx]Sheet1!\$A\$2:\$A\$xxxxx,1,false)),"",1)

are both files open ?

this is just on two sheets, as an example here

=IF(ISERROR(VLOOKUP(A1,Sheet2!\$A\$1:\$A\$4,1,FALSE)),"",1)

so if there is an error - ie cannot find the lookup then as its true it returns nothing "" if it is true it returns 1

File size:
8.8 KB
Views:
51
As Seen On