Excel V LOOKUP

[LucyK]

Hi All,

I am looking for some advice with regard to V LOOKUP. My company has not followed a strict naming convention within its database. For Example, some records have been created as 'Amazon' some have been created as 'Amazon LTD' some have been created as lower case and upper case, and some with brackets and some without.

I need to analyze the spend on each supplier, but unfortunately, it has become a manual process due to the poor naming convention. V LOOKUP true or false doesn't give me the information I need. Is there a more sophisticated way of achieving this through formulas to avoid manual input?

Any help would be appreciated

thanks
Lucy

 

[jasonb75]

You could try using MATCH with wildcards.

Example vlookup formula

=VLOOKUP(F2,A:D,4,FALSE)

MATCH equivalent with wildcards.

=INDEX(D:D,MATCH("*"&F2&"*",A:A,FALSE))