Problem with MATCH function

**djuk** · 04-03-2009, 06:46 AM

Hi All,

I'm trying to get to grips with an issue around the MATCH function in excel but I'm fast running out of ideas. It seems that some records, regardless of how they are pasted (values or rich format) or cleaned (CLEAN, TRIM, or LEFT) there are a handful of problem records which just will not match in any scenario.

Has anybody seen anything like this before? I have linked to a screenshot below illustrating a particular record not matching whilst being in the array - formulas included for reference.

http://www.hicup.org/excelmatch.jpg

Thanks

David

**arthurbr** · 04-03-2009, 06:53 AM

Hi and welcome to the board,

it would be easier to help if you posted a zipped worksheet instead of a picture

**djuk** · 04-03-2009, 06:59 AM

Thanks for the suggestion, zipped workbook at the link below..

http://www.hicup.org/Book1.zip

**DonkeyOte** · 04-03-2009, 07:01 AM

Totally agree with arthurbr, images are rarely helpful.

A quick way to try and identify a problem is to use alternative approaches to see which / if any return a result

G4: =MATCH(TRIM(C4),A:A,0)
H4: =MATCH(C4&"*",A:A,0)

If the former returns a value you have trailing spaces etc in the criteria but not the data, if the latter returns a value you (most likely) have trailing spaces in the lookup range values but not the criteria