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 ...
Excel Functions
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.

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

        SergeiBaklan , thanks, Sergei, for you continued help. When I input the formula in the 'Account Code' column, it returns a blank. What I'm trying to do is have the formula return, for example, '6300' in cell D5, '6900' in cell D6, etc. Could you show me how to do this? Please see attached the sample file. 

Resources