Lookup value and present as number

[datastudent]

Hi,

I want to see if my ID has finished the program FSAP, SSAP or JSAP but I want to show it as 1 if complete and 0 or blank if not completed. Hope somebody can help me

Table 1 shows how I want the result to look like in column B:D, while table 2 is my basis if the ID has completed the program.

Incumbent Dashboard 2023.xlsx
	A	B	C	D	E	F	G
1	ID	FSAP	SSAP	JSAP		ID	Program
2	1234	1	1			1234	FSAP
3	5678	1	1			5678	FSAP
4	9012	1				9012	FSAP
5	1356			1		1356	JSAP
6	8024			1		8024	JSAP
7						1234	SSAP
8						5678	SSAP
Sheet1

 

[etaf]

how about
=IF(COUNTIFS($F$2:$F$8,$A2,$G$2:$G$8,B$1)=0,"",1)

Book6
	A	B	C	D	E	F	G
1	ID	FSAP	SSAP	JSAP		ID	Program
2	1234	1	1			1234	FSAP
3	5678	1	1			5678	FSAP
4	9012	1				9012	FSAP
5	1356			1		1356	JSAP
6	8024			1		8024	JSAP
7						1234	SSAP
8						5678	SSAP
Sheet1

Cell Formulas
Range	Formula
B2:D6	B2	=IF(COUNTIFS($F$2:$F$8,$A2,$G$2:$G$8,B$1)=0,"",1)

 

[datastudent]

how about
=IF(COUNTIFS($F$2:$F$8,$A2,$G$2:$G$8,B$1)=0,"",1)

Book6
	A	B	C	D	E	F	G
1	ID	FSAP	SSAP	JSAP		ID	Program
2	1234	1	1			1234	FSAP
3	5678	1	1			5678	FSAP
4	9012	1				9012	FSAP
5	1356			1		1356	JSAP
6	8024			1		8024	JSAP
7						1234	SSAP
8						5678	SSAP
Sheet1

Cell Formulas
Range	Formula
B2:D6	B2	=IF(COUNTIFS($F$2:$F$8,$A2,$G$2:$G$8,B$1)=0,"",1)

It works. Thanks a lot!

 

[etaf]

You are welcome