I have 2 worksheets with potentially the same data. Sheet 1 column A has VIN
numbers and Sheet2 column F has VIN numbers. I want to see which VIN Number
appears in both worksheets. What formula should I use?
--
Jeff W

If one list is shorter than the other compare the shorter list to the longer
list.

One way:

=IF(COUNTIF(Sheet2!F:F,A1),"In both","")

Or:

=IF(COUNTIF(Sheet1!A:A,F1),"In both","")

Copy down as needed


--
Biff
Microsoft Excel MVP


"Jeff W" wrote in message
...
I have 2 worksheets with potentially the same data. Sheet 1 column A has
VIN
numbers and Sheet2 column F has VIN numbers. I want to see which VIN
Number
appears in both worksheets. What formula should I use?
--
Jeff W

Depends, I suppose, on where you want the answer to show. I sometimes have a
situation where - well, say I have a log of incidents including the user ID,
and a master list of contractor IDs on a separate sheet; I want a column on
the log to indicate whether the ID on this row appears on the contractor list
(ie whether this ID belongs to a contractor rather than an employee), and a
column on the master contractor sheet to indicate whether this contractor
appears in the log. I use MATCH for the purpose: Some column on Sheet1 says

=MATCH(RC1,Sheet2!C6,0)

If the ID in column 1 appears in column 6 on Sheet2, what I see here is a
row number; if not, I get #VALUE. Not pretty, but my eye can pick it out
effortlessly. Or you can make it a little prettier like this:

=IF(ISERROR(MATCH(RC1,Sheet2!C6,0)),"","OtherList" )

This yields a blank if the VIN isn't on Sheet2, or "OtherList" if it is.

And by the way, the fussbudget says "it's not a VIN number, it's just a
VIN". Argh.

--- "Jeff W" wrote:
I have 2 worksheets with potentially the same data. Sheet 1 column A has VIN
numbers and Sheet2 column F has VIN numbers. I want to see which VIN
Number appears in both worksheets. What formula should I use?