Look up and match functions

[mkvsam]

The attached spreadsheet has a list of modules and their corresponding marks and pass or fail status. On the right (two columns that are highlighted in yellow), I need to fill up the status of the modules using look-up and match functions or any other suitable Excel functions.

 

[Sam_D_Ben]

Please check:

Book1
	G	H	I	J
3	Semester 1	Status	Semester 2	Status
4	Module 1	Pass	Module 7	Pass
5	Module 2	Fail	Module 8	Pass
6	Module 3	Pass	Module 9	Pass
7	Module 4	Pass	Module 10	Pass
8	Module 5	Pass	Module 11	Pass
9	Module 6	Pass	Module 12	Pass
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=IF(LOOKUP(2,1/($A$2:$A$15=$G4),$C$2:$C$15)="P","Pass","Fail")
J4:J9	J4	=IF(LOOKUP(2,1/($A$2:$A$15=$I4),$C$2:$C$15)="P","Pass","Fail")

Thanks

 

[DanteAmor]

Hello and welcome to MrExcel.

Just comment that in your example module 10 says "Fail", but I think it should say "Pass".

I was building this formula, but @Sam_D_Ben posted before.

varios 10ene2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Modules	Mark	Status
2	Module 1	70	P
3	Module 2	37	F				Semestre 1	Status	Semestre 2	Status
4	Module 3	79	P				Module 1	Pass	Module 7	Pass
5	Module 4	45	F				Module 2	Fail	Module 8	Pass
6	Module 5	60	P				Module 3	Pass	Module 9	Pass
7	Module 6	66	P				Module 4	Pass	Module 10	Pass
8	Module 2	66	F				Module 5	Pass	Module 11	Pass
9	Module 4	38	P				Module 6	Pass	Module 12	Pass
10	Module 7	38	P
11	Module 8	51	P
12	Module 9	53	P
13	Module 10	53	P
14	Module 11	75	P
15	Module 12	61	P
16
17
Hoja2

Cell Formulas
Range	Formula
J4:J9,H4:H9	H4	=IF(INDEX($C$2:$C$15,LOOKUP(2,1/($A$2:$A$15=G4),ROW($A$2:$A$15))-ROW($A$2)+1)="P","Pass","Fail")

 

[mkvsam]

Both look up functions work well. How do I solve this problem if I need to pick up electives passed from table 1 to the highlighted cells (gray). As there is no single choice of electives, cell reference for look up is not working. Rather, I need to check the whole list of electives and check which electives the students studied and only pick up passed ones as attached

 

[rajeshcbe1976]

Both look up functions work well. How do I solve this problem if I need to pick up electives passed from table 1 to the highlighted cells (gray). As there is no single choice of electives, cell reference for look up is not working. Rather, I need to check the whole list of electives and check which electives the students studied and only pick up passed ones as attached


View attachment 104778

I think this is not possible, as there are three things to do. First, one needs to match third-table items with first-table items. Then check the B or C column cell values. And use the lookup function to capture the status in the second table.

 

[DanteAmor]

Both look up functions work well. How do I solve this problem if I need to pick up electives passed from table 1 to the highlighted cells (gray).

Try:

varios 10ene2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Modules	Mark	Status
2	Module 1	70	P
3	Module 2	37	F				Semestre 1	Status	Semestre 2	Status
4	Module 3	79	P				Module 1	P	Module 7	P
5	Module 4	45	F				Module 2	F	Module 8	P
6	Module 5	60	P				Module 3	P	Module 9	P
7	Module 6	66	P				Module 4	P	Module 10	P
8	Module 2	66	F				Module 5	P	Module 11	P
9	Efective 1	20	F				Module 6	P	Module 12	P
10	Module 4	38	P				Efective	P	Efective	F
11	Module 7	38	P
12	Module 8	51	P
13	Efective 2	26	F
14	Module 9	53	P
15	Module 10	53	P
16	Module 11	75	P
17	Module 12	61	P
18	Efective 2	55	P
19	Efective 3	56	F
Hoja2

Cell Formulas
Range	Formula
H4:H10	H4	=INDEX($C$2:$C$19,LARGE(IF(LEFT($A$2:$A$19,LEN(G4))=G4,ROW($A$2:$A$19)),COUNTIF($G$4:$G$10,G4)+COUNTIF($I$4:$I$10,G4))-1)
J4:J10	J4	=INDEX($C$2:$C$19,LARGE(IF(LEFT($A$2:$A$19,LEN(I4))=I4,ROW($A$2:$A$19)),COUNTIF($I$4:$I$10,I4))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mkvsam]

Try:

varios 10ene2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Modules	Mark	Status
2	Module 1	70	P
3	Module 2	37	F				Semestre 1	Status	Semestre 2	Status
4	Module 3	79	P				Module 1	P	Module 7	P
5	Module 4	45	F				Module 2	F	Module 8	P
6	Module 5	60	P				Module 3	P	Module 9	P
7	Module 6	66	P				Module 4	P	Module 10	P
8	Module 2	66	F				Module 5	P	Module 11	P
9	Efective 1	20	F				Module 6	P	Module 12	P
10	Module 4	38	P				Efective	P	Efective	F
11	Module 7	38	P
12	Module 8	51	P
13	Efective 2	26	F
14	Module 9	53	P
15	Module 10	53	P
16	Module 11	75	P
17	Module 12	61	P
18	Efective 2	55	P
19	Efective 3	56	F
Hoja2

Cell Formulas
Range	Formula
H4:H10	H4	=INDEX($C$2:$C$19,LARGE(IF(LEFT($A$2:$A$19,LEN(G4))=G4,ROW($A$2:$A$19)),COUNTIF($G$4:$G$10,G4)+COUNTIF($I$4:$I$10,G4))-1)
J4:J10	J4	=INDEX($C$2:$C$19,LARGE(IF(LEFT($A$2:$A$19,LEN(I4))=I4,ROW($A$2:$A$19)),COUNTIF($I$4:$I$10,I4))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks for your solution. It is not working for me for some reason. I am using 2019 version.
Secondly, I want to update two cells highlighted in gray with electives (from table 1) that the student passed. For example student studied elective 1 and failed. Studied elective 2 and 3 passed. Elective 2 should go to cell G10 and elective 3 should go cell I10 as the student passed these.

 

[DanteAmor]

Elective 2 should go to cell G10 and elective 3 should go cell I10 as the student passed these.

That is the result presented by the formulas, as you can see in the example I gave, and it also works in 2019, it is the version that I use.

But for it to work you must edit the formula and press the 3 keys at the same time Shift + Control + Enter

 

[mkvsam]

That is the result presented by the formulas, as you can see in the example I gave, and it also works in 2019, it is the version that I use.

But for it to work you must edit the formula and press the 3 keys at the same time Shift + Control + Enter

Thanks. But the query is to replace the strings in G10 and I10 cells with the names of the electives (e.g., elective 1, elective 2, etc.) that the student passed from table 1.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Match lookup functions
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.