CODEWITHSUNDEEP
excelif-statementexcel-formulamatchcell
D Alam
D Alam

Reputation: 21

Matching Chosen Options to Correct Answers in Excel

I have a list of correct answers in Col G for four questions, located in cells G3:G6. In Col B (Options), there are questions and answer options – starting with question 1 in cell B2 and its answer options in B3, B4, B5, and B6. This pattern continues with question 2 in cell B7 and its options in the following cells. In Col C (Response), the chosen options, is depicted with '1' and '0' for unselected ones. I need an Excel formula to check if the chosen option matches with the correct answers in Column G. If the response is correct, I want it to be marked as 'Yes,' otherwise, mark it as 'No'.

Here is the formula I worte =IF(AND(B3<>"", INDEX(G$3:G$6, MATCH(B3, $B3:$B6, 0)) = C3), "Yes", "No")

however I'm not getting the desired result.. for Illustrative purposes I've created a possible result column and highlighted "Yes" in red. But I'm getting "No". Any help is appreciated. enter image description here

Upvotes: -1

Views: 547

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Copy and paste the formula in cell D2

=IF(C2="","",IF((C2=1)*(ISNUMBER(XMATCH(B2,$G$3:$G$6))),"Yes","No"))

Assuming you have a current version of office with lambda function you could give a try

=MAP(C2:C21,B2:B21,LAMBDA(ss,bd,IF(ss="","",IF((ss=1)*(ISNUMBER(XMATCH(bd,$G$3:$G$6))),"Yes","No"))))

Upvotes: 0

Black cat
Black cat

Reputation: 6271

Try this formula in column F

=IF(AND(IFERROR(MATCH(B3,G$3:G$6,0)>0,FALSE),C3=1),"YES","NO")

and conditional formatting

Highlight Cell Rules -> Equal to ... -> type YES and select Red Text press OK

Upvotes: 1

Related Questions