SOLVED

Formula Help to retrieve a name from table and return a value

%3CLINGO-SUB%20id%3D%22lingo-sub-2740269%22%20slang%3D%22en-US%22%3EFormula%20Help%20to%20retrieve%20a%20name%20from%20table%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2740269%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20might%20be%20complicated%20as%20I'm%20not%20extremely%20familiar%20with%20excel.%20I%20would%20like%20to%20create%20a%20formula%20that%20can%20find%20a%20name%20from%20the%20bottom%20table%20and%20depending%20on%20which%20section%20it%20is%20in%20(cleanup%20%2F%20setup%20etc.)%20I%20want%20it%20to%20be%20able%20to%20add%201%20to%20the%20value%20assigned%20in%20the%20upper%20table.%20If%20this%20isn't%20possible%2C%20ill%20understand...%3C%2FP%3E%3CP%3E%26nbsp%3B%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%20image-alt%3D%22excel%20help.png%22%20style%3D%22width%3A%20930px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F309627i33F87050E035B468%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22excel%20help.png%22%20alt%3D%22excel%20help.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2740269%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2740412%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20to%20retrieve%20a%20name%20from%20table%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2740412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1152326%22%20target%3D%22_blank%22%3E%40lopezn6%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20C25%2C%20confirmed%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%3C%2FSTRONG%3E%20to%20turn%20it%20into%20an%20%3CSTRONG%3Earray%20formula%3C%2FSTRONG%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24A%2448%3A%24A%2461%3DC%2424)*(IFERROR(FIND(%24B25%2C%24B%2448%3A%24M%2461)%2C0)%3D1))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right%20to%20F25%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2740457%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20to%20retrieve%20a%20name%20from%20table%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2740457%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1152326%22%20target%3D%22_blank%22%3E%40lopezn6%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20forgot%20to%20mention%3A%20change%20D26%20from%20'Set%20up'%20to%20Setup%2C%20to%20be%20consistent%20with%20A52%3AA55.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2740483%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%20to%20retrieve%20a%20name%20from%20table%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2740483%22%20slang%3D%22en-US%22%3EHey%20thank%20you%20so%20much%2C%20the%20function%20works%20great.%20I%20am%20noticing%20that%20rows%2049%3A51%20under%20cleanup%20arent%20registering%20when%20i%20type%20a%20name%20into%20the%20section%3C%2FLINGO-BODY%3E
New Contributor

This might be complicated as I'm not extremely familiar with excel. I would like to create a formula that can find a name from the bottom table and depending on which section it is in (cleanup / setup etc.) I want it to be able to add 1 to the value assigned in the upper table. If this isn't possible, ill understand...

 

 

excel help.png

4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@lopezn6 

In C25, confirmed with Ctrl+Shift+Enter to turn it into an array formula:

 

=SUMPRODUCT(($A$48:$A$61=C$24)*(IFERROR(FIND($B25,$B$48:$M$61),0)=1))

 

Fill to the right to F25, then fill down.

@lopezn6 

I forgot to mention: change D26 from 'Set up' to Setup, to be consistent with A52:A55.

Hey thank you so much, the function works great. I am noticing that rows 49:51 under cleanup arent registering when i type a name into the section

@lopezn6 

It works for me - see the attached sample workbook (I didn't bother with the formatting)

Please note that Robbins occurs in the lower table but not in the upper one, so he is not counted.