Column match formula - with a twist

[Beatnik10]

Hi Guys,

Essentially i have two separate sheets. One contains around 400 rows of information pertaining to stores and is the 'complete list' (Sheet 2). I then have another with around 250 rows which include updates (sheet 1), these 250 are within the list of 400.

What i'm looking for is a formula which can check the reference number on each sheet and where there are matches, mark the list of 400 in a separate column with a character.

In basic terms...

If "A1, sheet 2" matches anything in "column J, sheet 1" then fill "AP1, sheet 1" with 'X' otherwise leave blank/fill with 'Z' (which ever is easier)

I can then easily extract the lines which require an update and do this en masse

I've been fiddling around but can't crack this one, any ideas ladies and gents?

Many thanks in advance

[ChemistB]

In AP1 copied down

=IF(ISNUMBER(MATCH(A1, Sheet1!$J$1:$J$500,0)), "X","")
Does that work for you?

[Beatnik10]

It didn't i'm afraid - not sure why though as from the looks of it i can't see any issues? (not that i'm an expert of course, thats why i'm here!!!)

Changed the "" at the end to "Z" and it populated all with that so it is running, just not matching?

[ChemistB]

Correct. Whatever is in A1 is not an exact match for what's in J

If they are numbers, make sure that both are stored as numbers or stored as text. (In empty cell, for example = ISNUMBER(J5) should return true for both J5 and A1

Look for extra spaces at begging and end of strings (=LEN(J5) should tell you the # of characters)

Or you could upload the spreadsheet and we could have a look at it (Go Advanced>Manage Attachments)