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.
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
Sage reporting packs in Excel to go. No set-up required.
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.
Match is for row reference
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.
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
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)
Convert to Text or Value
You can use functions =text or =Value to one of the "entry" and get correct result.
Regards
Zubar Edhy