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

 

[alansidman]

In I2

=IF(AND(E2="",G2=""),C2,IF(AND(E2<>"",C2<>"",H2=""),E2,G2))

 

[Snakehips]

Is this too simplistic?

Book3
	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
7
Sheet12

Cell Formulas
Range	Formula
I2:I7	I2	=IFERROR(INDEX(C2:I2,COUNTA(C2:H2)-1),"")

HTH

 

[arthurbr]

In I1 and pull down

=Lookup(2,1/(isnumber($a1:$h1)),$a1:$h1)

 

[Peter_SSs]

.. another very simple one

24 10 10.xlsm
	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
Last Score

Cell Formulas
Range	Formula
I2:I6	I2	=LOOKUP(9^9,C2:G2)

 

[GMC The Macro Man]

Hi team,
Thanks to everyone for replying and any of the results would have been great, however when i gave this to my client, they asked if I could do this with their grade and not the score and also they have added another date

Some of the formulas above only deal with numbers but as this is text it would need to be changed so from this NEW table, would you be able to help me find the formula that will look at columns D,F,H and J and return their LAST grade in Column K.
Thanks again

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

 

[Peter_SSs]

For me it is quite a similar formula for text:

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

Cell Formulas
Range	Formula
K2:K6	K2	=LOOKUP("zz",C2:J2)

 

[GMC The Macro Man]

For me it is quite a similar formula for text:

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

Cell Formulas
Range	Formula
K2:K6	K2	=LOOKUP("zz",C2:J2)

Hi Peter_SSs, thanks for looking.
Your formula, was quite simplistic which was great, 1st one just looking at numbers and the 2nd looking at text, but I know this client and they keep changing things so what if they were to change the format of the score (which I have seen them do for their reporting)
For instance on column E, the results could be this,
E2= 85+
E3= 52>
E4= 74
E5= 84<A
E6= 99
So they are not number formats and may in fact sometimes be text. It seems strange but it is the way they report some of their results, so if I had to, what would be the best way to just look at columns D,F,H & J and report the last grade
Thanks again

 

[Peter_SSs]

but I know this client and they keep changing things

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?

 

[GMC The Macro Man]

In I2

=IF(AND(E2="",G2=""),C2,IF(AND(E2<>"",C2<>"",H2=""),E2,G2))

Hi there, thanks for looking at my query,
The client has added an extra date for checking so with your formula, how would i make the change to capture this.

Thanks again

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