Extract the last value in the row with a condition

[sofas]

Welcome. Please help in extracting the last test that was completed, subject to the subject number, so that when
writing the subject number in column (H), the name of the last test with a value in column (i)
is obtained. It does not matter, whether by formula or vba code.

 

[Eric W]

Try:

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Materials	test1	test2	test3	test4	test5		Materials	The last test
2	1	4		6				1	test3
3	2		5			9		2	test5
4	3	52		69				3	test3
5	4			11		102		4	test5
6	5							5	#N/A
7	6		104					6	test2
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=LOOKUP(2,1/INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0),$B$1:$F$1)

 

[sofas]

Try:

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Materials	test1	test2	test3	test4	test5		Materials	The last test
2	1	4		6				1	test3
3	2		5			9		2	test5
4	3	52		69				3	test3
5	4			11		102		4	test5
6	5							5	#N/A
7	6		104					6	test2
Sheet1

Cell Formulas
Range	Formula
I2:I7	I2	=LOOKUP(2,1/INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0),$B$1:$F$1)

Very cool, this is what needs to be done. Is there a possibility to add the phrase Not Found inside the formula when no value is found instead of #N/A?

 

[Eric W]

Sure,

=IFERROR(LOOKUP(2,1/INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0),$B$1:$F$1),"Not found")

Glad I could help!

 

[sofas]

Sure,

=IFERROR(LOOKUP(2,1/INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0),$B$1:$F$1),"Not found")

Glad I could help!

Very good thank you very much I appreciate your help

 

[sofas]

Sure,

=IFERROR(LOOKUP(2,1/INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0),$B$1:$F$1),"Not found")

Glad I could help!

Thank you again. I have greatly benefited from your suggestion. One last request, please. What can I change if I want to get the first value that is opposite to what is in the previous suggestion?

 

[Eric W]

One way:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Materials	test1	test2	test3	test4	test5		Materials	The last test	First test
2	1	4		6				1	test3	test1
3	2		5			9		2	test5	test2
4	3	52		69				3	test3	test1
5	4			11		102		4	test5	test3
6	5							5	Not found	Not found
7	6		104					6	test2	test2
Sheet2

Cell Formulas
Range	Formula
I2:I7	I2	=IFERROR(LOOKUP(2,1/INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0),$B$1:$F$1),"Not found")
J2:J7	J2	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$F$1)/(INDEX($B$2:$F$7,MATCH(H2,$A$2:$A$7,0),0)<>""),1)),"Not found")

I think there's an easier way, but the new functions (Excel 2021 and newer) have spoiled me and I've forgotten how to do things. But this should work for you. Incidentally, if you change the 15 in that formula to a 14, it works the same as the prior formula to get the last test.