Show last score in table

[GMC The Macro Man]

Hi everyone

On the table below, I'm trying to write a formula that will look at columns C,E and H and return their LAST score in Column I.
I would have been able to do this if the 3 columns where together, but because they're in separate columns, I'm struggling.
Any help would be greatly appreciated

TIA
GMC

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Name	Oct score	Oct grade	Jan Score	Jan Grade	Mar score	Mar Grade	Last score
2	P1234	John	70	C+	80	B	78	C+	78
3	P1235	Robert	60	C	72	C+			72
4	P1236	Alice	80	B	68	C	92	A	92
5	P1237	Helen	56	C					56
6	P1238	Thomas	95	A+	90	A	85	B+	85
Sheet1

 

[GMC The Macro Man]

Hard to cover every possibility. Next thing they will have 3 scores per month and want the middle one of the second last month.

What version of Excel is this client using? (or perhaps they keep changing that too? )

Have you tried any of the other suggestions above?

Hi Peter SS_s

You are probably right and looking at the above, the suggestion from alansidman would work best as it picks the columns as opposed to a range.
Thanks again

 

[Peter_SSs]

the suggestion from alansidman would work best as it picks the columns as opposed to a range.

It will get very complicated if many more months are added!

Did you try the suggestion by @Snakehips?

.. and is there an answer to my question about the client's Excel version?

This would be more readily expandable and also cover the case that somebody did not get a score and/or grade in the middle as in the last 2 rows below (though these may not be the desired results), as well as if the scores are not numerical (row 1).

24 10 10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Name	Oct score	Oct grade	Dec score	Dec grade	Jan Score	Jan Grade	Mar score	Mar Grade	Last grade	Last Score
2	P1234	John	70	C+	85	B	80	B	78+	C+	C+	78+
3	P1235	Robert	60	C	52	C	72	C+			C+	72
4	P1236	Alice	80	B	74	C	68	C	92	A	A	92
5	P1237	Helen	56	C		Z					Z	56
6	P1238	Thomas	95	A+					85	B+	B+	85
Last Grade (2)

Cell Formulas
Range	Formula
K2:K6	K2	=LOOKUP(2,1/(C2:J2<>"")/(RIGHT(C$1:J$1,5)="Grade"),C2:J2)
L2:L6	L2	=LOOKUP(2,1/(C2:J2<>"")/(RIGHT(C$1:J$1,5)="Score"),C2:J2)

 

[GMC The Macro Man]

Hi Peter_SSs
your solution here gives me both options that would suit my needs so thank you very much.
They use Office 2021, same as myself
GMC

 

[Peter_SSs]

your solution here gives me both options that would suit my needs so thank you very much.

You're welcome.

They use Office 2021, same as myself

Thanks. That does provide another alternative ..

24 10 10.xlsm
	C	D	E	F	G	H	I	J	K	L
1	Oct score	Oct grade	Dec score	Dec grade	Jan Score	Jan Grade	Mar score	Mar Grade	Last Grade	Last Score
2	70	C+	85	B	80	B	78+	C+	C+	78+
3	60	C	52	C	72	C+			C+	72
4	80	B	74	C	68	C	92	A	A	92
5	56	C		Z					Z	56
6	95	A+					85	B+	B+	85
Last Grade (2)

Cell Formulas
Range	Formula
K2:K6	K2	=LET(f,FILTER(C2:J2,(RIGHT(C$1:J$1,5)="Grade")*(C2:J2<>""),""),INDEX(f,COLUMNS(f)))
L2:L6	L2	=LET(f,FILTER(C2:J2,(RIGHT(C$1:J$1,5)="Score")*(C2:J2<>""),""),INDEX(f,COLUMNS(f)))

 

[GMC The Macro Man]

You're welcome.


Thanks. That does provide another alternative ..

24 10 10.xlsm
	C	D	E	F	G	H	I	J	K	L
1	Oct score	Oct grade	Dec score	Dec grade	Jan Score	Jan Grade	Mar score	Mar Grade	Last Grade	Last Score
2	70	C+	85	B	80	B	78+	C+	C+	78+
3	60	C	52	C	72	C+			C+	72
4	80	B	74	C	68	C	92	A	A	92
5	56	C		Z					Z	56
6	95	A+					85	B+	B+	85
Last Grade (2)

Cell Formulas
Range	Formula
K2:K6	K2	=LET(f,FILTER(C2:J2,(RIGHT(C$1:J$1,5)="Grade")*(C2:J2<>""),""),INDEX(f,COLUMNS(f)))
L2:L6	L2	=LET(f,FILTER(C2:J2,(RIGHT(C$1:J$1,5)="Score")*(C2:J2<>""),""),INDEX(f,COLUMNS(f)))

Thank you

 

[Peter_SSs]

Cheers.