Forum Discussion
I need a function to return mark from any two ranges.
Hi
I need a function to be placed in cells (Q3:T5) to return the mark either from range 1 or range 2 based on the student name and stage (First, Second, Third, fourth ) in range 3. The number of rows in Range1, Range 2, and Range 3 is variable. see attachment, Thanks
A6, A9 and H11 contain "student 1". This is inconsistent, it should be "student1". Otherwise the formulas will fail.
In Q3:
=IFERROR(INDEX($C$3:$C$15,MATCH(1,($A$3:$A$15=$P3)*($E$3:$E$15=Q$2),0)),INDEX($J$3:$J$13,MATCH(1,($H$3:$H$13=$P3)*($L$3:$L$13=Q$2),0)))
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
Fill to column T and to row 5.
A6, A9 and H11 contain "student 1". This is inconsistent, it should be "student1". Otherwise the formulas will fail.
In Q3:
=IFERROR(INDEX($C$3:$C$15,MATCH(1,($A$3:$A$15=$P3)*($E$3:$E$15=Q$2),0)),INDEX($J$3:$J$13,MATCH(1,($H$3:$H$13=$P3)*($L$3:$L$13=Q$2),0)))
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
Fill to column T and to row 5.
- Thanks...
