SOLVED

Iron Contributor

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

2 Replies
best response confirmed by ajl_ahmed (Iron Contributor)
Solution

Re: I need a function to return mark from any two ranges.

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.

Re: I need a function to return mark from any two ranges.

Thanks...
1 best response

Accepted Solutions
best response confirmed by ajl_ahmed (Iron Contributor)
Solution

Re: I need a function to return mark from any two ranges.

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.