Excel: MATCH does not match two apparently identical cells

Excel: MATCH does not match two apparently...

Didn't find your answer?

I am comparing two TBS to see which accounts in TB1 are not present in TB2.
TB1 has the account numbers in column A
TB2 has the account numbers in column C

The account numbers are in different formats so in column O, in TB2 I have the formula =TRIM(LEFT(C820,4)) to take the first 4 characters of the account number and delete any trailing empty space.
In TB1, I have a column with the formula =MATCH(A121,'TB2'!$O$566:$O$822,0), to try to find accounts in TB1 that have no equivalent in TB2

However:
The formula in row 820 in TB2 returns "407, but in TB1, the formula in the same row as a/c 407 returns #N/A, i.e. it fails to match the two "407"s.
There seem to be no extraneous spaces and I have tried to format both ranges as text.

Can anyone suggest anything else I can do to get my Match formula to recognise that that two "407"s are equal. I have about 120 rows with the same problem.

Replies (10)

Please login or register to join the discussion.

By bro0010
25th Aug 2014 17:10

Is one a number and the other text?

Hi,

I find the formula bar is very helpful in these circumstances. Let me explain.

Say the two things you want to compare are in A1 and A2. Write a formula in A3 as =A1=A2.

If they match you should see TRUE as the result. If they don't match you should get FALSE as the result. I think you've described such a situation even though you think the answer should be TRUE. To find out why: highlight A1 in the formula bar and press F9 - you'll see the A1 cell reference converted into the contents of the A1 cell; then highlight A2 in the formula bar and press F9, again you'll see the A2 cell reference converted into the contents of the A2 cell. The two will look different in some way and you'll have your answer. Normally I find that it is something like =407="407" and the answer is that A1 contains the number 407 and A2 contains a text string with three characters in it 4, 0 and 7. The two are not the same and hence the FALSE.

I hope this helps.

Regards

Ian

Onion Reporting Software Ltd

www.onionrs.co.uk

Sage reporting packs in Excel to go. No set-up required.

Thanks (3)
By bro0010
24th Aug 2014 15:54

Did you mean "407?

Hi again,

I assumed on first reading that when you said The formula in row 820 in TB2 returns "407 that you'd made a typo in including the ".

Perhaps it is your answer: "407 does not equal 407

Regards

Ian

PS If you would like to send me an example of your problem I'd be happy to have a look at it for you.

Thanks (1)
Replying to johnt27:
avatar
By lukayl
24th Aug 2014 13:21

Match is for row reference

bro0010 wrote:

Hi again,

I assumed on first reading that when you said The formula in row 820 in TB2 returns "407 that you'd made a typo in including the ".

Perhaps it is you answer: "407 does not equal 407

Regards

Ian

PS If you would like to send me an example of your problem I'd be happy to have a look at it for you.

It looks like a typo, as I understand Match doesn't return the exact value in the target, but the reference row number instead.  I think it would be better if OP can share some example for us to look into.

Thanks (1)
avatar
By chatman
25th Aug 2014 12:31

Thanks for the help both of you. The formula bar tip was useful as it told me which one of the two was formatted as text, and which as a number, so I was able to go back and use a formula to convert from one format to another.

Thanks (0)
Replying to Tim Vane:
By bro0010
25th Aug 2014 17:09

You're welcome. Glad to help.

Thanks (0)
avatar
By Uther
19th Sep 2014 16:15

Double minus (double unary)

Another useful tip is that if you want to match numbers formatted as text and normal numbers you can force excel to see the text as a number by putting -- before it. For example:

Column A formatted as text , column E formatted as number

Look up value from A in column E and return the value in column F ("Exists")

=VLOOKUP(A5,E:F,2,0) returns #N/A as the text does not match the number

=VLOOKUP(--A5,E:F,2,0) returns "Exists" as Excel is forced to recognise A5 as a number

Thanks (1)
avatar
By Fastlane
20th Sep 2014 03:33

Treat Text that looks like a number as a Number

An alternative to New's suggestion of using a double negative is to multiply the text (that looks like a number) x 1. So in chatman's case, modifying the formulae as follows (underlined characters) will result in each text value being converted to a number. Both formulae need to be done to ensure you have all apples instead of a fruit salad!!

=TRIM(LEFT(C820,4))*1

=MATCH(A121*1,'TB'2!$O$566:$O$822,0)

Thanks (1)
avatar
By edhy
22nd Sep 2014 05:28

Convert to Text or Value

You can use functions =text or =Value to one of the "entry" and get correct result.

Regards

Zubar Edhy

Thanks (1)
avatar
By chatman
22nd Sep 2014 16:04

Numbers formatted as text

Great tips everyone. Thanks.

Thanks (0)
avatar
By Jim Hays
09th Oct 2018 20:03

The presence of the ~ (tilde) character can also cause identical cells to fail to MATCH.

Thanks (0)