Forum Discussion
Using Xlookup to find an keyword match.
I am using xlookup to find the name of Owner1 in Sheet1 within the Status column below, and return the status of Sheet2 Owner1, column C Status. The problem is Owner1 occupies more than one row in Sheet2, so xlookup only returns the first match which is "Out Of Scope". Can I use an IF statement to incorporated and return only the word "Complete"?
Sheet1
Sheet2:
2 Replies
Does this do what you want?
=IFS(COUNTIFS(Sheet2!$A$2:$A$30, A2, Sheet2!$C$2:$C$30, "Complete"), "Complete", COUNTIFS(Sheet2!$A$2:$A$30, A2, Sheet2!$C$2:$C$30, "In Progress"), "In Progress", COUNTIFS(Sheet2!$A$2:$A$30, A2, Sheet2!$C$2:$C$30, "Out of Scope"), "Out of Scope", TRUE, "")
- Perfect Hans; thank you so much.
