Xavier,

For your example, enter this array formula (enter using Ctrl-Shift-Enter) into cell C1, then copy
down to C2:C5

=SUM((TEXT(A1,"0")=LEFT($B$1:$B$3,LEN(A1)))*1)=1

HTH,
Bernie
MS Excel MVP


"Xavier" wrote in message
ps.com...
I'm having trouble applying the right formula to my spreadsheet. What
I'm trying to do is compare two columns and determine which numbers in
column A appear in column B. The problem is the way my data is stored:

A B
12345 12345r1
12345 23456
12345 67890r2
23456
34567

As you can see, I'll either have an exact match (23456) or a partial
match (12345 & 12345r1). So I would need, say column C, to display
True, True, True, True, False. I tried the formula
"=ISNUMBER(MATCH(LEFT(A1,5)&"*",List2!A:A,0))+ 0" but that returns True
only for partial matches, not exact. Does anyone know which formula I
should use for my case? Thank you!

Xavier