using vlookup to find exact match

Hi,
I have two spreadsheets with same field called IDs.
I want to know whether the IDs in the ID column of the first sheet is in
the ID column of the second.
I want to create a new column on the first sheet to return Yes/No depending
on the result. I think I could use vlookup formula but have no idea how.
Can anyone help me?
Thank you!

--
Message posted via http://www.officekb.com

Janice

One way:

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"no","yes")

and drag down. Assumes your ID column is column A on both sheets.

Regards

Trevor


"Janice Lee via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have two spreadsheets with same field called IDs.
> I want to know whether the IDs in the ID column of the first sheet is in
> the ID column of the second.
> I want to create a new column on the first sheet to return Yes/No
> depending
> on the result. I think I could use vlookup formula but have no idea how.
> Can anyone help me?
> Thank you!
>
> --
> Message posted via http://www.officekb.com

Janice Lee via OfficeKB.com wrote:
> Hi,
> I have two spreadsheets with same field called IDs.
> I want to know whether the IDs in the ID column of the first sheet is in
> the ID column of the second.
> I want to create a new column on the first sheet to return Yes/No depending
> on the result. I think I could use vlookup formula but have no idea how.
> Can anyone help me?
> Thank you!
>
Without a lookup formula

=IF(COUNTIF(Sheet2!A:A,A1)>0,"yes","no")

filled down as far as required.

Alan Beban

For the given formula,

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"no","yes")

What is "ISNA" and what does each value separated with comma represent?
Thanks in advance!

--
Message posted via http://www.officekb.com

Janice -
The formula Alan gave you should work. What the formula is saying is IF the Vlookup does not find a match (thats what ISNA means), then bring back a "no" otherwise bring back a "yes". The Vlookup statement itself is broken down as follows:
A1 represents the cell where your ID'd start in sheet 1

Sheet2!A:A represents where you want to search for a match (in this case, on sheet 2 in all of column A)

1 means bring back the 1st column in your range (which in this case is only column A, so you only have 1 column)

false means you want an exact match

I hope this helps. Good luck!