On Tue, 17 May 2011 07:33:30 -0700 (PDT), wrote:

Ok here is the link for the file.
http://uploading.com/files/5274ec51/Formula%2Btest.xls/

See the 'Info' tab for what I am trying to accomplish. Thanks

I've had a chance to look at your spreadsheet.

Your data isn't matching what you think it is, therefore you are not going to return anything.

You want to use data in column I to match items in C2:C26 and return what is in the same row in Col E of the two spreadsheets.

But consider:

I161: 42 AUTUMNWOOD CV

You expect to see, as noted in N161: MCMP

But in C2:C26, there is no Autumnwood.

With regard to

700 W FOREST AVE

There is no way it can match with

700 West Forest Suite 200


One is not included in the other.

Also:

I99: 1700 WOODLAWN AVE
N99: MedSouth

But the only thing similar in C2:C26 is:

17X0 Woodlawn

Again, this will not match with your current rules.

I note that many of your desired MCMP matches seem to be based on Column F data and not Column I data. Obviously, none of them will work. That is also true for others of your desired matches: sometimes referring to COL I but other times to COL F

---------------------------------------------

The formula works for the rule that you stated which had to do with matching text in one column with the text in another column and returning information a few columns over. But your rule does not describe what you want to do.

You are going to need much more specific rules that what you have supplied in order to develop a matching system that will be non-ambiguous.

As of now, sometimes you want to match the DestinationAddress1; sometimes the ShipperAddress1, but how do you decide?

Also, it seems as if you want to use some kind of fuzzy matching, and that is beyond what I can do for you here. You are going to have to "clean up" the data, or come up with some very specific rules for matching fragments.