SOLVED

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

Iron Contributor

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

@ajl_ahmed 

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.

1 best response

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

@ajl_ahmed 

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.

View solution in original post