Two way lookup in a row for last row value

[sdking]

Hello

I have a spreadsheet that my colleagues have been using for many years and want to maintain the structure of, but would like something new adding in - so unfortunately I can't change the layout to something more functional.

Worksheet one (/Data) lists the test results for a group of students similar to the below:

The UID cell is A1 and the column is made of merged cells, similarly column B is merged cells.

Worksheet two (/Summary) is the new aspect that will be a summary of the students. My colleagues would like for it to have the last value in each row for each of the students by topic, as below:

The UID cell is A1.

In the green squares with the ???s, my colleagues would like formulas that would lookup the UID and topic from Summary in Data and return that last result/last cell in that row.

Does anyone know if that's possible? Many thanks in advance, I hope it's quite clear but please let me know if you'd like clarification.

Thanks

SD

 

[JoeMo]

Merged cells are aesthetically pleasing, but frequently difficult to deal with. Below is a solution that depends on a couple of additions:
1. Remove the merges so that UID and Name appear in every row. If you like you can change the font to match the cell background so they aren't visible. I have done this in the example below.
2. Add a column to the Data that returns the most recent scores.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	UID	Name	Topic	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023	Most Recent Score
2	2345H	H Jones	Language	8			14					9				9
3	2345H	H Jones	Maths		12				15				13			13
4	2345H	H Jones	Science			12			10		19					19
5	6876C	C Jones	Language	3			14			9				13		13
6	6876C	C Jones	Maths			15			14			14				14
7	6876C	C Jones	Science		14			16			13	15				15
8
9
10
11			Most Recent Scores
12	UID	Name	Language	Maths	Science
13	2345H	H Jones	9	13	19
14	6876C	C Jones	13	14	15
Sheet8

Cell Formulas
Range	Formula
P2:P7	P2	=LOOKUP(1E+306,D2:O2)
C13:E14	C13	=INDEX($A$2:$P$7,MATCH(($A13&C$12),($A$2:$A$7)&($C$2:$C$7),0),16)

 

[Herakles]

Hello

I have a spreadsheet that my colleagues have been using for many years and want to maintain the structure of, but would like something new adding in - so unfortunately I can't change the layout to something more functional.

Worksheet one (/Data) lists the test results for a group of students similar to the below:
View attachment 91580
The UID cell is A1 and the column is made of merged cells, similarly column B is merged cells.

Worksheet two (/Summary) is the new aspect that will be a summary of the students. My colleagues would like for it to have the last value in each row for each of the students by topic, as below:
View attachment 91581
The UID cell is A1.

In the green squares with the ???s, my colleagues would like formulas that would lookup the UID and topic from Summary in Data and return that last result/last cell in that row.

Does anyone know if that's possible? Many thanks in advance, I hope it's quite clear but please let me know if you'd like clarification.

Thanks

SD

I assume that this grows horizontally as dates are added and that you don't want to have to alter formula to accommodate that.

The existing layout is not very functional but as you say, this cnanot be changed.

Combinations.xlsm
	A	B	C	D	E
1	UID	Name	Language	Math	Science
2	1234H	H Jones	41	16	44
3	5678C	C Jones	49	19	16
4	9101T	T Jones	40	37	40
5	1123P	P Jones	11	33	13
Summary

Cell Formulas
Range	Formula
C2:C5	C2	=IFNA(LOOKUP(1E+306,OFFSET(Data!$A$1,MATCH($A2,Data!$A:$A,0)-1,3,1,COUNTA(Data!$1:$1)-3)),"")
D2:D5	D2	=IFNA(LOOKUP(1E+306,OFFSET(Data!$A$1,MATCH($A2,Data!$A:$A,0),3,1,COUNTA(Data!$1:$1)-3)),"")
E2:E5	E2	=IFNA(LOOKUP(1E+306,OFFSET(Data!$A$1,MATCH($A2,Data!$A:$A,0)+1,3,1,COUNTA(Data!$1:$1)-3)),"")

 

[Peter_SSs]

I think that a sort of combination of the two suggestions above but avoiding helper columns, change of layout and the volatile function OFFSET can be achieved.
It does assume that the Language, Maths, Science rows do appear for each student and in the same order and also that is the same order as at the top of the results - as per your sample.

IFNA could be used to hide the #N/A result as per post #3 but I thought that it may be prudent to highlight it if a student has no result for a particular subject.

Where I have used 99, use any number that is sure to be larger that any student value.

23 05 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Topic	1/01/2023	2/01/2023	3/01/2023	4/01/2023	5/01/2023	6/01/2023	7/01/2023	8/01/2023	9/01/2023	10/01/2023	11/01/2023	12/01/2023
2	H Jones	Language	8			14					9
3		Maths		12				15				13
4		Science			12			10		19
5	C Jones	Language	3			14			9				13
6		Maths			15			14			14
7		Science		14			16			13	15
8	T Jones	Language		10
9		Maths
10		Science				15
11
12
13
14	Name	Most recent language	Most recent maths	Most recent science
15	H Jones	9	13	19
16	C Jones	13	14	15
17	T Jones	10	#N/A	15
Latest Results

Cell Formulas
Range	Formula
C15:E17	C15	=LOOKUP(99,INDEX($D$2:$O$10,MATCH($B15,$B$2:$B$10,0)-1+COLUMNS($C:C),0))

 

[Herakles]

The tendancy to use short and fat rather than long and thin data sets with additional columns used instead of additional rows for only when
there is something to represent makes subsequent analysis more difficult. Long and thing data sets tend to enable more flexibility.

Turning one layout into the other, even just for the purposes of analysis, would not be too difficult.

In this case, the layout was inherited so cannot be changed.

I did not know that OFFSET was a volatile function Peter_SSs. Is there a list of these somewhere?

 

[Peter_SSs]

I did not know that OFFSET was a volatile function Peter_SSs. Is there a list of these somewhere?

Follow the 'volatile function' link in my previous post.