• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

lookup formula based on a partial cell reference

L

Leftee

New Member
Hi, I have been trying to figure out how to make write a formula that will take one column and match another column based on only a partial value. My example is as follows.

I have one list with Supplier names in the first column and dollar amounts in the second column. I have another list with Supplier names in one column and dolloar amounts in the second column. The supplier names are not always spelled the same. Abreviations and punctuation could be different. I need to try to match them up so I can combine the dollar amounts


File 1 - Supplier Name

ABC, Inc. $2000

DCE LLC. $4000

XXX $1000


File 2 - Supplier Name

ABC Inc $500

Dce $200

xxx, co. $100


I need to comine ABC, INC. and ABC Inc and add the dollar amounts together.


Any help would be greatly appreciated !!!!!
 
Looks like you'll need to do some sort of "fuzzy" search.

http://chandoo.org/wp/2008/09/25/handling-spelling-mistakes-in-excel-fuzzy-search/
 
I can try this but I am not sure this will work. Is there anything where I can say something like - look a the first 4 or 5 letters, or the first word and ignore the punctuation. It isnt that the Suppliers are mispelled, its more like the CO or Inc or LLC at the end and the punctuation.
 
The lookup functions support the use of wildcards, so you could certainly do something like:

=VLOOKUP(LEFT(A2,3)&"*",OtherList,2,FALSE)


Which would search OtherList of entries where the first 3 characters match the first 3 characters of cell A2.
 
You must log in or register to reply here.
Back
Top