I am using index match to look up a value (test score) based on the student selected. No problems, working fine.
Now I'd like the returned test score to be change according to the assignment selected.
I've got 2 ranges at the moment storing the test scores (and there may be more in future) and have set up radio buttons to select which range to read the result from based on the student name as the lookup value.
It's working if I have the test results showing in different cells...ie. 2 difference index match functions.
But I want the test result in the same cell.
I've tried using an IF statement but whilst it continues to work if the first option button is selected, I get FALSE if the second one is selected.
=IF(AU5=1,INDEX(U5:U13,MATCH(A5,A5:A30,0),IF(AU5=2,INDEX(AQ5:AQ18,MATCH(AF5,AF5:AF18,0)))))
Can anyone see the problem or select a better way?
Thanks
Liz
Now I'd like the returned test score to be change according to the assignment selected.
I've got 2 ranges at the moment storing the test scores (and there may be more in future) and have set up radio buttons to select which range to read the result from based on the student name as the lookup value.
It's working if I have the test results showing in different cells...ie. 2 difference index match functions.
But I want the test result in the same cell.
I've tried using an IF statement but whilst it continues to work if the first option button is selected, I get FALSE if the second one is selected.
=IF(AU5=1,INDEX(U5:U13,MATCH(A5,A5:A30,0),IF(AU5=2,INDEX(AQ5:AQ18,MATCH(AF5,AF5:AF18,0)))))
Can anyone see the problem or select a better way?
Thanks
Liz