Lookup Long Reference Number?

Pl see enclosed file with macro and Excel function. B5 value is not counted as 1 in A1:A5 in macro and function.
Pl upload file if more assistance is required.

Sorry for off-topic interjection:

ONCE AGAIN, although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Ref Post#3
@.AliGW
I am sorry for missing details.
Formula used is

=COUNTIF(A1:A5,B5)

Macro code is

Please Login or Register  to view this content.

Pl see file
Formula
=COUNTIF($C$1:$C$7,D5)
Macro code

Please Login or Register  to view this content.

In both the cases result is 0.

I think what happens here is that the COUNTIF() function is designed to receive a text string in the 2nd argument so that it can receive <, > type conditions. Internally, when the COUNTIF() function receives something that can be interpreted as a number, it converts it to a number (double precision) even when passed as text to the function.

Have you tried the COUNTIFS() function to see if it behaves the same? I don't have Excel here, so I cannot test for you, but you might try that to see if COUNTIFS() is the same.

You call this a lookup function, but I usually don't think of COUNTIF() as a lookup function. Have you tried an actual lookup function (like MATCH())? MATCH() is usually better at distinguishing between numbers and numbers as text. Of course, rather than testing for a count of 0 or not 0, you will be testing for "did MATCH() return a number or did it return an error". I know you have to be careful how you write the statement, because one way of calling the worksheet function will cause VBA to terminate anytime the function returns an error (preventing you from testing for the error condition afterwards) and another way of calling the worksheet function will store the error and allow you to test for the error afterwards. I'm not savvy enough to remember exactly how to do it (it has to do with whether or not you use the WorksheetFunction collection to call the function or not).

Are you limited to Excel? I have LO Calc here, and LO Calc's COUNTIF() function can distinguish between 12345678901234567890 and 12345678901234568907, so it doesn't seem to have the same problem as Excel's implementation. Of course, where this is part of a macro, that could mean a lot of extra work trying to convert your macro into a language that another spreadsheet can understand.

If push comes to shove, you can always write your own countif type function in VBA where you can control if, when, and how VBA converts a number as text to a number.

A quick internet search found this rundown on error handling when calling worksheet functions: https://rubberduckvba.wordpress.com/...on-and-errors/

Prepending a non-numeric character might be easiest.

Am I missing something? How about just ...

	A	B	C
1	3421112223334440000	2	B1: =SUMPRODUCT(--(A1 = $A$1:$A$8))
2	3421112223334440000	2
3	3421112223334440001	1
4	3421112223334440002	1
5	3421112223334440010	1
6	3421112223334440400	1
7	3421112223334440009	1
8	3421112223334440230	1

Try this alternate way
Function
=SUMPRODUCT(1*($C$1:$C$7=D5))
Macro code

Please Login or Register  to view this content.