SOLVED

# Combining IFS(SEARCH

Occasional Contributor

# Combining IFS(SEARCH

I have a student quiz. I'm parsing a phrase to see if the student answered "survey" (case insensitive).
I'm assigning 1 point for a correct answer
.8 for the spelling survay
.5 for servay
Else 0
It's bugging on survay. It's not even seeing the else true, "0"
Ideas?

=IFS(SEARCH("survey",F2),"1",SEARCH("survay",F2),".8",SEARCH("servay",F2),".5",TRUE,"0")

John in Québec
11 Replies

# Re: Combining IFS(SEARCH

@jtjohnston_quebec The problem is that SEARCH returns an error value if the term is not found.

So if the student answers anything but "survey", the first SEARCH causes the entire formula to return an error; the rest of the formula isn't evaluated.

Apart from that, I'd make the function return numbers such as 0.8 instead of text strings such as "0.8".

=IFS(IFERROR(SEARCH("survey",F2),0),1,IFERROR(SEARCH("survay",F2),0),.8,IFERROR(SEARCH("servay",F2),0),.5,TRUE,0)

# Re: Combining IFS(SEARCH

@jtjohnston_quebec

Or

=IFS(ISNUMBER(SEARCH("survey",F2)),1,ISNUMBER(SEARCH("survay",F2)),0.8,ISNUMBER(SEARCH("servay",F2)),0.5,TRUE,0)

best response confirmed by jtjohnston_quebec (Occasional Contributor)
Solution

# Re: Combining IFS(SEARCH

As variant

``=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})``

Thanks
.
Why isnumber?

Thanks for this

# Re: Combining IFS(SEARCH

That explains it. I thought ifs was more supple

# Re: Combining IFS(SEARCH

@Hans Vogelaarwhy did you use isnumber. why check to see if it is a number?

# Re: Combining IFS(SEARCH

@Sergei BaklanI have an answer "Servay " that got evaluated as 0.

What's wrong?

=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})

# Re: Combining IFS(SEARCH

It works

If you are on Excel which doesn't support dynamic arrays try to use SUMPRODUCT() instead of SUM(). Please check the file attached.

# Re: Combining IFS(SEARCH

Very possible. I was using Google sheets. I'll go through it again. Thanks

# Re: Combining IFS(SEARCH

Sorry, can't help with Google Sheets, have no idea where it could be the difference.