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

Frequent Contributor


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 (Frequent Contributor)


A6, A9 and H11 contain "student 1". This is inconsistent, it should be "student1". Otherwise the formulas will fail.

In Q3:




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.