matching numbers with different digits orders

hi, i have part numbers which are 3 digits numbers and 4 digits numbers, they may have leading zeros too.

let's try the 3-digit numbers first (if it works, it should work for 4-digits numbers)

column A listing the part numbers
column B is another set of numbers i need to match
column C is the value to display on column D if A&B "matches"
column D is the result (taken from column C)

A & B "matches" if B has all the numbers in A, but not necessarily in the same order.

for example:

A B C D
123 213 tokyo tokyo (A & B matches)
123 124 osaka #n/a (doesn't match)
055 505 osaka osaka (A & B matches)
5566 6556 kyoto kyoto (A & B matches)


MATCH function won't work i guess.

i've tried "disecting" column A into 3 separate columns listing a single digit
=LEFT(A1,1) returns 1
=MID(A1,2,1) returns the second digit 2
=RIGHT(A1,1) returns 3

then i tried using combination of IF, AND, OR fucntions to try make it work. and it's a mess

suggestions welcome.
thanks,

Correction...

=IF((SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))=LEN(B1))*(LEN(A1)=LEN(B1)),C1,#N/A)

Hope this helps!

Originally Posted by Domenic

Try...

D1, copied down:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))=LEN(B1),C1,#N/A)

Hope this helps!

oh my..u guys sure are fast

Domenic,
the formula will return
0021 and 0321 as "matched" or
1200 and 2110 as "matched" too

(EDIT: your correction formula same results)

btw, i have to format column A & B as TEXT because of leading zeros.


duane,
i'm looking thru your method,

thanks

Originally Posted by foxxkat

Domenic,
the formula will return
0021 and 0321 as "matched" or
1200 and 2110 as "matched" too

(EDIT: your correction formula same results)

Yep, I just checked it and you're right. Even with my corrected formula it returns the wrong results. I have to get going, but I'll give it some more thought tomorrow.

Cheers!