Forum Discussion
Solved
Finding return value from multiple columns/cells with specific text
Hello,
I have to verify multiple cells (C2 to F2) with two possible statuses: "Eligible" and "Not Eligible". In Excel, would it be possible to:
- If all cells "Eligible", then the result is "Passed".
- If any of the columns is "Not Eligible", then the result is "Failed".
- (additional issue) this is not super important but it would be helpful to add one more condition: if any of the columns is "Consult", then the result is "No Result". I'm fine if only the 1st and 2nd issue works, though.
I have tried finding solutions in other discussions, but usually the formula that I copy-pasted keep showing "Passed" even if one of the documents is listed as "Not Eligible".
Thanks in advance.
| A | B | C | D | E | F | G | |
| 1 | No. | Name | Document 1 | Document 2 | Document 3 | Document 4 | Result |
| 2 | 1 | Candidate A | Eligible | Eligible | Eligible | Eligible | Passed |
| 3 | 2 | Candidate B | Eligible | Not Eligible | Eligible | Eligible | Failed |
| 4 | 3 | Candidate C | Eligible | Eligible | Consult | Eligible | No Result |
| 5 | 4 | Candidate D | Eligible | Eligible | Eligible | Not Eligible | Failed |
yes, try this:
=IFS(AND(D3:G3="Eligible"),"Passed",OR(D3:G3="Consult"),"No Result",OR(D3:G3="Not Eligible"),"Failed",1,"Error")here is the result:
I will try to attach the file...
4 Replies
Hi g_pramono,
Apply below formula in cell G2:
=LET( doc, C2:F5, IF(BYROW(doc = "Consult", OR), "No Result", IF(BYROW(doc = "Eligible", AND), "Passed", "Failed")) )Hope this helps.
IlirU
yes, try this:
=IFS(AND(D3:G3="Eligible"),"Passed",OR(D3:G3="Consult"),"No Result",OR(D3:G3="Not Eligible"),"Failed",1,"Error")here is the result:
I will try to attach the file...
trying again...
