Find a column and then apply if

[vmjan02]

I am not sure as how will this function work,

if should first check the column header and then apply if and function, can any can help on it.

=IF(AND(INDEX('Candidate Master'!$A$1:$CC$1,MATCH("Cand. Final Status",'Candidate Master'!$A$1:$CC$1,0))="Rejected",INDEX('Candidate Master'!$A$1:$CC$1,MATCH("Email Flag",'Candidate Master'!$A$1:$CC$1,0))=""),1,"")

Blow is the sample data, output as 1 will be in column d row wise.

Tech Remarks	HOD Remarks	Final Remarks	Cand. Final Status
dummy	dummy	don't remove this dummy data
			Rejected
			Rejected
			On-Hold
			Selected
			To Be Offered
			Joined
			Not Joined
			On-Hold
			Rejected
			On-Hold
			Rejected
			Selected
			Selected
			To Be Offered
			Rejected