Excel help - anyone any good with finding matches?

[Happyotamus]

I have a list of 4000 numbers, and a list of 600 numbers, some of which are duplicated in the 4000 numbers- how do I automatically highlight all the duplicates please?

I did a while back with text and it worked seamlessly but today it's being a twit so I guess it is actually me being stupid and getting the format wrong.

I am not looking for the find and replace function - it's a specific formula I need.

Use the MATCH function which will output the number where it’s found in the big list if there is a match or #N/A if not. If you’d prefer to output true or false for match or not use ISNUMBER(MATCH).

If you will then get more elaborate VLOOKUP is useful.

I would probably do a v look up between the two sheets and see which numbers come across so are the duplicates

Thank you - with that one can I input the entire range of 600 or do I need to go 1 by 1?

Very, very grateful for your help!

The right function depends on what you intend to do after you have found a match.

Or do a vlook up function of 600 in 4000, not duplicate will appear as n/a

You just drag formula down in your 4000 list, that's all, then filter out n/as, untick them and you have all your duplicates on show

Not sure of the list but can't you select all then remove duplicates..

www.excel-easy.com/examples/index-match.html

Put the 600 in one column, the 4000 in another. Put a vlookup against the 4000 looking up the 600 numbers. If it’s there the number will appear. If it doesn’t then n/a will appear.

Alternatively add the 600 to the 4000 but colour them to highlight them. Then use conditional formatting to highlight the duplicates sorting by the numbers.

You can highlight duplicate values easily in conditional formatting without using formulae

Ok so if there's a match, I need to copy the rows that match is on for all matches as the other columns in those rows contain data that we need.

(Thank you so much!)

1. Sort them into order
2. Highlight the column with duplicate numbers
3. Select Conditional Formatting in the the Home tab
4. Select Highlight cell rules
5. Select Duplicate Values

Anything duplicated will be highlighted in red