Help on substitue value, by excel function

[Guest]

i receive BIG data files with customer name, location, bill number, date and
due amount, reason for delay in payment etc.,
But customer is spelled in many ways and sent to me.
However some portion will be unique.
for example:
M/s Mariamman transport
Mariamman transport
Om srimariamman transport
Omganesh srimariamman transport
but all the above are equal to one registered customer name
Mariamman Transport Co Ltd.,

i will be having sheet2 with short name and correct customer name
like in the above example
short name as: mariamman
Correct name as : Mariamman Transport Co Ltd.,

I NEED FUNCTION AT SHEET1:C5
pick incorrect customer name in sheet1:d5, find if any shortname listed in
sheet2 is suitable, if match is found then in that row pick the correct
customer name and display (at sheet1:c5, where function i require)

shortname & correct customer name will be in Sheet2:b5:c400
so everytime i receive customer data, i will insert sheet2

 

[Don Guillett]

Maybe a look in the vba help index for FIND or FINDNEXT will be helpful.

 

[Guest]

How about a Lookup? Preferably with the short Names sorted on column A

=VLOOKUP(PROPER(D4),PROPER('CO Names'!A1:B28),2,0) entered as an Array
formula (Ctrl + Shift + Enter)

I named the sheet to copy CO Names to avoid confusion when pasting into a
new workbook

Regards
Peter