Forum Discussion
HELP WITH FORMULAS TO AUTO-POPULATE A TABLE
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
shyam_amara , you are welcome. Another useful function is IFERROR() to wrap any error, not only #N/A
6 Replies
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.
shyam_amara , I added some parameters not to hardcode everything (and better to use named ranges or tables)
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.
- 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.
shyam_amara , you are welcome. Another useful function is IFERROR() to wrap any error, not only #N/A
