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

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

If the Col_B items are ALL text entries...maybe something like this?:

With your sample data beginning in A1

This formula checks if the A1 value is in the B1:B3 list
C1: =COUNTIF($B$1:$B$3,A1&"*")0

Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bernie Deitrick" wrote:

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

Apologies, Bernie....Evidently, I selected the wrong posting in this topic. :\
(I intended to reply to the OP)

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

If the Col_B items are ALL text entries...maybe something like this?:

With your sample data beginning in A1

This formula checks if the A1 value is in the B1:B3 list
C1: =COUNTIF($B$1:$B$3,A1&"*")0

Copy that formula down

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bernie Deitrick" wrote:

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

Thanks guys, got it working now!

Xavier

Bernie Deitrick wrote:
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