Forum Discussion

shyam_amara's avatar
shyam_amara
Copper Contributor
Apr 09, 2019
Solved

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 ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 10, 2019

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 09, 2019

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.

  • shyam_amara's avatar
    shyam_amara
    Copper Contributor
    Apr 09, 2019
    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.