Compare Two Columns (Exact and Wildcard)

[Xavier]

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

 

[Bernie Deitrick]

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

 

[Ron Coderre]

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

 

[Ron Coderre]

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

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

XL2002, WinXP

 

[Xavier]

Thanks guys, got it working now!

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