SOLVED
Home

HELP WITH FORMULAS TO AUTO-POPULATE A TABLE

%3CLINGO-SUB%20id%3D%22lingo-sub-419619%22%20slang%3D%22en-US%22%3EHELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-419619%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20for%20help%20with%20the%20attached%20sheet%2C%20where%20I%20have%20student%20names%20in%20one%20column%20and%20would%20like%20to%20extract%20their%20marks%20in%20English%20scored%20in%20H1%20and%20H2.%3C%2FP%3E%3CP%3EIn%20the%20current%20sheet%20I%20have%20entered%20the%20marks%20manually%20but%20is%20there%20a%20way%20to%20auto%20populate%20the%20two%20table%20in%20colum%20L%3AN%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%20in%20advance%2C%3CBR%20%2F%3EShyam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-419619%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-420571%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-420571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317796%22%20target%3D%22_blank%22%3E%40shyam_amara%3C%2FA%3E%20%2C%20I%20added%20some%20parameters%20not%20to%20hardcode%20everything%20(and%20better%20to%20use%20named%20ranges%20or%20tables)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20499px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106447i39872EE701F6B45B%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%3EFormula%20in%20M4%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(%24E%243%3A%24H%2422%2CMATCH(1%2CINDEX((%24B%243%3A%24B%2422%3D%24L4)*(%24D%243%3A%24D%2422%3DM%242)*(%24C%243%3A%24C%2422%3D%24L%241)%2C0)%2C0)%2CMATCH(%24L%242%2C%24E%242%3A%24H%242%2C0))%2C0)%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20down%20and%20to%20the%20right.%20MATCH(1%2CINDEX(...%20here%20filters%20your%20data%20and%20returns%20proper%20row.%20Second%20MATCH%20return%20the%20column%20(English%20in%20this%20case).%20If%20nothing%20found%20formula%20returns%20zero.%20Better%20use%20it%20rather%20than%20empty%20string%20if%20you%20will%20do%20other%20calculations%20with%20data%3B%20and%20apply%20custom%20format%20to%20all%20marks%20as%3C%2FP%3E%0A%3CPRE%3EGeneral%3BGeneral%3B%22-%22%3C%2FPRE%3E%0A%3CP%3Eto%20hide%20zeros%20from%20the%20interface.%3C%2FP%3E%0A%3CP%3EIn%20attached%20file%20that%20done%20for%20the%20first%20range%20with%20marks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-420855%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-420855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317796%22%20target%3D%22_blank%22%3E%40shyam_amara%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20alternative%20strategy%20using%20SUMIFS%20to%20return%20the%20required%20values.%3C%2FP%3E%3CP%3E%3D%20SUMIFS(%20Results%5BEnglish%5D%2C%20Results%5BMatch%20Number%5D%2C%20match%2C%20Results%5BStudent%20Name%5D%2C%20Class1Name%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20chosen%20to%20enter%20the%20formula%20as%20an%20array%20formula%20but%20implicit%20intersection%20will%20also%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-422282%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-422282%22%20slang%3D%22en-US%22%3EThanks%20Sergei.%20I%20must%20say%20the%20IFNA%20is%20a%20real%20saver%20for%20me.%20Did%20not%20know%20that%20such%20a%20function%20exists!%20I%20use%20lot%20of%20Vlookup%20and%20was%20always%20pestered%20by%20the%20%23N%2FA.%20Now%20I%20know%20how%20to%20deal%20with%20it.%20Thanks%20much.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-424852%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-424852%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317796%22%20target%3D%22_blank%22%3E%40shyam_amara%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20Another%20useful%20function%20is%20IFERROR()%20to%20wrap%20any%20error%2C%20not%20only%20%23N%2FA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-442660%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-442660%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%3Bthanks%20for%20your%20help.%20I%20have%20another%20problem%20that%20needs%20to%20be%20solved.%20I%20have%20mentioned%20the%20problem%20in%20the%20link%20below.%20Could%20you%20please%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FLookup-Pattern-Cascading%2Fm-p%2F440291%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FLookup-Pattern-Cascading%2Fm-p%2F440291%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-445448%22%20slang%3D%22en-US%22%3ERe%3A%20HELP%20WITH%20FORMULAS%20TO%20AUTO-POPULATE%20A%20TABLE%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-445448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317796%22%20target%3D%22_blank%22%3E%40shyam_amara%3C%2FA%3E%26nbsp%3B%2C%20is%20says%20the%20post%20was%20deleted%3C%2FP%3E%3C%2FLINGO-BODY%3E
shyam_amara
New Contributor

I am looking for help with the attached sheet, where I have student names in one column and would like to extract their marks in English scored in H1 and H2.

In the current sheet I have entered the marks manually but is there a way to auto populate the two table in colum L:N 


Thanks in advance,
Shyam

6 Replies

@shyam_amara , I added some parameters not to hardcode everything (and better to use named ranges or tables)

image.png

Formula in M4

=IFNA(INDEX($E$3:$H$22,MATCH(1,INDEX(($B$3:$B$22=$L4)*($D$3:$D$22=M$2)*($C$3:$C$22=$L$1),0),0),MATCH($L$2,$E$2:$H$2,0)),0)

drag it down and to the right. MATCH(1,INDEX(... here filters your data and returns proper row. Second MATCH return the column (English in this case). If nothing found formula returns zero. Better use it rather than empty string if you will do other calculations with data; and apply custom format to all marks as

General;General;"-"

to hide zeros from the interface.

In attached file that done for the first range with marks.

@shyam_amara 

An alternative strategy using SUMIFS to return the required values.

= SUMIFS( Results[English], Results[Match Number], match, Results[Student Name], Class1Name )

 

I have chosen to enter the formula as an array formula but implicit intersection will also work.

Thanks Sergei. I must say the IFNA is a real saver for me. Did not know that such a function exists! I use lot of Vlookup and was always pestered by the #N/A. Now I know how to deal with it. Thanks much.
Solution

@shyam_amara , you are welcome. Another useful function is IFERROR() to wrap any error, not only #N/A

@Sergei Baklan thanks for your help. I have another problem that needs to be solved. I have mentioned the problem in the link below. Could you please help?

 

https://techcommunity.microsoft.com/t5/Excel/Lookup-Pattern-Cascading/m-p/440291

@shyam_amara , is says the post was deleted

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