Forum Discussion

lucykennedy66's avatar
lucykennedy66
Copper Contributor
Nov 14, 2022

Excel VLOOKUP / Poor Naming Convention

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 also have instances where the country name is displayed after the supplier name, and also some random numbers and spelling mistakes. It's a very messy inconsistent database.

My Challenge is 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 accurate 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

excel
Formulas and Functions
office 365

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 14, 2022

    lucykennedy66 

    =SUMPRODUCT(ISNUMBER(SEARCH(B$2,$E$5:$E$14))*$F$5:$F$14)

    Since you want to calculate the spend for a supplier you can try SUMPRODUCT.

     


     

  • Charla74's avatar
    Charla74
    Iron Contributor
    Nov 14, 2022
    It sounds as though Vlookup may not be the best solution for what you are trying to achieve. It sounds as though your data would contain multiple results (eg. Amazon, AMAZON LTD, UK Amazon, etc), whereas the Vlookup function stops searching once it finds the first match within the data. Possibly consider Pivot Tables where you can filter and adjust results within a field.

Resources