Current formula searches data source for student name and quiz#. If quiz not taken yet, returns blank. If quiz taken, returns data in Column 5. Source data comes from Google form, which is transferred to Google Sheets and then saved to Excel.
Need to use formula below to now search Column 8 (calculated value) for MAX value and return what is in Column 5 for "smithjoeQ3" but can't get the correct formula. Thank you.
=IF(ISERROR(VLOOKUP(CONCATENATE(Students!$M$7&"q2"),Data!$B$4:$G$951,5,FALSE)),"",(VLOOKUP(CONCATENATE(Students!$M$7&"q2"),Data!$B$4:$G$951,5,FALSE)))
"....now search Column 8 (calculated value) for MAX value and return what is in Column 5 for "smithjoeQ3"....."
Do you mean you want the absolute max value in column 8.....or the max value for rows where column B contains "smithjoeQ3"? Note that in your VLOOKUP "column5" is actually F because that's the 5th column of the lookup array (B4:G951), so when you say "Column 8" do you mean column H or the 8th column starting at B (column I)?