SOLVED

HELP WITH FORMULAS TO AUTO-POPULATE A TABLE

Copper 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.
best response confirmed by shyam_amara (Copper Contributor)
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

1 best response

Accepted Solutions
best response confirmed by shyam_amara (Copper Contributor)
Solution

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

View solution in original post