Home

Help with assigning account codes to one table from another table

%3CLINGO-SUB%20id%3D%22lingo-sub-887006%22%20slang%3D%22en-US%22%3EHelp%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887006%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20find%20and%20assign%20account%20codes%20for%20monthly%20accounting%20books%20I%20have%20for%20my%20business.%20What%20I'd%20like%20excel%20to%20do%20is%20search%20for%20text%20in%20a%20column%20(%22Name%22%20column%20shown%20below)%20and%2C%20if%20specific%20text%20is%20found%20in%20a%20cell%20(%22Text%20field%22%20column%20shown%20below)%2C%20return%20the%20account%20code%20from%20the%20table%20on%20the%20right%20with%20the%20specific%20text.%20Please%20see%20image%20below.%20Is%20there%20a%20function%20I%20can%20use%20to%20automate%20the%20process%20of%20assigning%20account%20codes%20to%20various%20expenses%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F134977i2FEB1D9EC0090395%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-10-01%20at%2011.27.58%20AM.png%22%20title%3D%22Screen%20Shot%202019-10-01%20at%2011.27.58%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-887006%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-887222%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887222%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418240%22%20target%3D%22_blank%22%3E%40Gsurfer210%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%3CACCOUNT%20code%3D%22%22%20range%3D%22%22%3E%2CMATCH(1%2C--ISNUMBER(SEARCH(%3CTEXT%20range%3D%22%22%3E%2C%3CNAME%3E))%2C0))%2C%22%22)%3C%2FNAME%3E%3C%2FTEXT%3E%3C%2FACCOUNT%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhere%20the%20ranges%20are%20related%20columns%20and%20the%20%3CNAME%3E%20is%20the%20cell%20value%20for%20which%20your%20search%20the%20code.%3C%2FNAME%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890242%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890242%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%26nbsp%3Bfor%20your%20help.%20I'm%20new%20to%20excel%20and%20unfamiliar%20with%20what%20%3CACCOUNT%20code%3D%22%22%20range%3D%22%22%3E%20and%20%3CTEXT%20range%3D%22%22%3E%20would%20be%20in%20this%20case.%20If%20account%20code%20is%20in%20column%20H%2C%20would%20%3CACCOUNT%20code%3D%22%22%20range%3D%22%22%3E%20be%20H5%3AH8%20for%20example%3F%20I've%20included%20a%20second%20picture%20showing%20rows%20and%20columns%20below.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135225i50690331CA8F8A74%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-10-02%20at%207.54.40%20PM.png%22%20title%3D%22Screen%20Shot%202019-10-02%20at%207.54.40%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FACCOUNT%3E%3C%2FTEXT%3E%3C%2FACCOUNT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892346%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418240%22%20target%3D%22_blank%22%3E%40Gsurfer210%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20I%20meant%20under%20%3CACCOUNT%20code%3D%22%22%20range%3D%22%22%3E%20%24H%245%3A%24H%24100%2C%20%3CNAME%20range%3D%22%22%3E%20-%20%24C%245%3A%24C%24100%2C%20etc.%3C%2FNAME%3E%3C%2FACCOUNT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20always%20better%20to%20attach%20small%20sample%20file%20with%2Finstead%20of%20screenshort.%20Very%20few%20people%20will%20type%20data%20manually%20from%20screen%20picture%20into%20the%20file%20to%20illustrate%20how%20formula%20works%20on%20real%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894316%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20thanks%2C%20Sergei%2C%20for%20you%20continued%20help.%20When%20I%20input%20the%20formula%20in%20the%20'Account%20Code'%20column%2C%20it%20returns%20a%20blank.%20What%20I'm%20trying%20to%20do%20is%20have%20the%20formula%20return%2C%20for%20example%2C%20'6300'%20in%20cell%20D5%2C%20'6900'%20in%20cell%20D6%2C%20etc.%20Could%20you%20show%20me%20how%20to%20do%20this%3F%20Please%20see%20attached%20the%20sample%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894337%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418240%22%20target%3D%22_blank%22%3E%40Gsurfer210%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20forgot%20to%20mention%20you%20shall%20use%20it%20as%20an%20array%20formula%2C%20i.e.%20enter%20with%20combination%20of%20Ctrl%2BShift%2BEnter%20instead%20of%20Enter.%20Result%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20675px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135480iFC3AF14B65CD176A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894348%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThat%20worked.%20Thanks!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894360%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20assigning%20account%20codes%20to%20one%20table%20from%20another%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894360%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418240%22%20target%3D%22_blank%22%3E%40Gsurfer210%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gsurfer210
New Contributor

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?

 

Screen Shot 2019-10-01 at 11.27.58 AM.png

7 Replies

@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.

Thanks, @Sergei Baklan, 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. Screen Shot 2019-10-02 at 7.54.40 PM.png

@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.

@Sergei Baklan , 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. 

@Gsurfer210 

Sorry, I forgot to mention you shall use it as an array formula, i.e. enter with combination of Ctrl+Shift+Enter instead of Enter. Result is

image.png

and in attached file

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies