MATCH function - 2 columns w/ SIMILAR, not EXACT data

[Guest]

colu. 1 contains:

1001
vendor 43980
1001/1180/90 CAT 8
1001/1180/90/8

col. 2 contains:
1001
SD3 vendor number 43980
PO 45609
1001 1180 90 8

I need to match these 2 columns IF the values are the same, regardless of
text.

Any suggestions?

 

[Fred Smith]

Given your data, the following formulas will generate a match:

=a1=b1
=right(a2,5)=right(b2,5)
no match in row 3
=substitute(a4,"/"," ")=b4

However, if other date have different similarities, you may find if more
efficient to write a macro to toss out the extraneous stuff, so you end up
with exact matches.

 

[Guest]

Hi Fred,
I'm not very familiar with the Match function (altho' I use Vlookup
frequently)... woul dyou mind giving me an example of the formula written out
that captures what you have suggested? It would be most appreciated.
thank you, Jane

 

[Fred Smith]

The formulas I gave you will evaluate to TRUE or FALSE. I didn't provide any
information on the Match function, because I didn't see an application for
it.

As you are interested, Match works somewhat like Vlookup, but rather that
returning a cell value, it returns the position in the list (eg, the 5th
entry in the list).