Apr 09 2019 10:48 AM
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
Apr 09 2019 02:05 PM
@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.
Apr 09 2019 02:45 PM
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.
Apr 09 2019 08:36 PM
Apr 10 2019 03:09 AM
Solution@shyam_amara , you are welcome. Another useful function is IFERROR() to wrap any error, not only #N/A
Apr 11 2019 10:50 PM
@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
Apr 12 2019 07:36 AM
@shyam_amara , is says the post was deleted
Apr 10 2019 03:09 AM
Solution@shyam_amara , you are welcome. Another useful function is IFERROR() to wrap any error, not only #N/A