Forum Discussion

DottieMH's avatar
DottieMH
Copper Contributor
Jan 02, 2024

Need help with VLOOKUP

Hello Everyone, I am trying to create a rubric for my semester exams. I keep getting an error in the calculation column. Could someone please help me figure out what is wrong? I am selecting the 'Credit Type' for each student from a dropdown menu in columns B-U. I created a lookup table in columns W-Y. I used the same dropdown menu. Each task has a different credit value, this is the reason for the lookup table. I would appreciate any advice or help. I need to get these exams graded by Thursday. 

 

 

Formulas and Functions

1 Reply

  • DaeyunPablo's avatar
    DaeyunPablo
    Brass Contributor
    Jan 02, 2024

    DottieMH 

    VLOOKUP can't handle multiple criteria in multiple columns. Try INDEX and MATCH. 

    https://exceljet.net/formulas/index-and-match-with-multiple-criteria

     

    =INDEX($Y$2:$Y$61,MATCH(1,($W$2:$W$61=$B$1)*($X$2:$X$61=B2),0))

     

     

    In case you want to sum up the credits, try SUMIFS.

    https://exceljet.net/functions/sumifs-function

     

    =SUMIFS($Y$2:$Y$61,$W$2:$W$61,$B$1,$X$2:$X$61,B2)

     

Resources