This is probably formula 101, but I sure could use some help. I haven't used a formula in 10 years. I have two lists of several thousand numbers I need to compare to see if column A has a match in column B.
Compare Two Columns and Highlight Matches. If you want to compare two columns and highlight matching data, you can use the duplicate functionality in conditional formatting. Note that this is different than what we have seen when comparing each row. In this case, we will not be doing a row by row comparison. Two Ways to Compare Columns in Excel Method One: This Equals That. If you have two lists that should be identical. Method Two: VLookUp. Don’t run away! Even if you’ve never worked with a VLookUp before. Finishing Touches. The information that Excel found the value we were looking up isn’t.
How would I do this? It is a Vlookup formula right?
Thanks in advance for your help!
You could use VLOOKUP but there are better ways. Here's one of them...
Let's assume your data is in the range A2:B2000.
Insert a new column A so that now your data is in columns B and C.
Enter this formula in A2:
=IF(ISNUMBER(MATCH(B2,C:C,0)),'Match',')
Copy down to the end of data in column B. You can do this very quickly by using your mouse and left double clicking on the fill handle.
The fill handle is that little black square in the bottom right corner of the selected cell. Hover your mouse over the fill handle until the cursor changes from a 'fat plus sign' to a 'skinny plus sign'. When the cursor is a 'skinny plus sign' then left double click and the formula will be copied down to the end of data in column B.
--
Biff
Microsoft Excel MVP
Biff
Microsoft Excel MVP
How do I compare two Excel sheets to detect whether the data in column B of sheet 2 appears in column A of sheet 1?
jonsca
3,6941414 gold badges3030 silver badges4545 bronze badges
user254711user254711
1 Answer
use Match() built-in function
for example
if your data looks like this
then stick the below formula in column C
and if there is a match you will get
1
in column C, if there is no match an #N/A
. You can wrap around the match function to display a custom message. But I think the above answers your question. user222864