Forum Discussion

Gsurfer210's avatar
Gsurfer210
Copper Contributor
Oct 01, 2019

Help with assigning account codes to one table from another table

I'm trying to find and assign account codes for monthly accounting books I have for my business. What I'd like excel to do is search for text in a column ("Name" column shown below) and, if specific text is found in a cell ("Text field" column shown below), return the account code from the table on the right with the specific text. Please see image below. Is there a function I can use to automate the process of assigning account codes to various expenses?

 

Excel Functions

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 01, 2019

    Gsurfer210 

    That could be like

    =IFERROR(INDEX(<Account Code range>,MATCH(1,--ISNUMBER(SEARCH(<Text range>,<Name>)),0)),"")

    where the ranges are related columns and the <Name> is the cell value for which your search the code.

    • Gsurfer210's avatar
      Gsurfer210
      Copper Contributor
      Oct 03, 2019

      Thanks, SergeiBaklan, for your help. I'm new to excel and unfamiliar with what <account code range> and <text range> would be in this case. If account code is in column H, would <account code range> be H5:H8 for example? I've included a second picture showing rows and columns below. 

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 03, 2019

        Gsurfer210 

        Yes, I meant under <Account code range> $H$5:$H$100, <Name range> - $C$5:$C$100, etc.

         

        It's always better to attach small sample file with/instead of screenshort. Very few people will type data manually from screen picture into the file to illustrate how formula works on real data.

Resources