Forum Discussion
IFS(OR(ISNUMER(SEARCH( function
No its me, sorry..
=OR(IF(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619)),TRUE,FALSE))
the brackets were in the wrong place :-/
Hm, this still doesn't solve my original problem. I need the function to produce a certain category. And that category depends on which word the function finds. So if F619 contains a word from Sheet2!$A$2:$A$10 it should produce Sheet2!$B$2 and when F619 contains a word from Sheet2!$A$11:$A$20 it should produce Sheet2!$B$11.
I thought of:
IFS(ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619)),Sheet2!$B$2,ISNUMBER(SEARCH(Sheet2!$A$11:$A$20,F619)),Sheet2!$B$11)
But that one doesnt work for some reason. It gives #N/A except for Sheet2!$A$2, then it produces Sheet2!$B$2 like it should.
Perhaps
=IFS(SUM(--ISNUMBER(SEARCH(Sheet2!$A$2:$A$10,F619))),Sheet2!$B$2,SUM(--ISNUMBER(SEARCH(Sheet2!$A$11:$A$20,F619))),Sheet2!$B$11,TRUE,"")
(array formula)
This formula constantly gives Sheet2!$B$2 as a result even if I fill in something between Sheet2!$A$11:$A$20 in F619. Why did u use SUM(-- in this formula though? I don't understand.
Hi Roy,
Please check in attached, I only changed F619 on A1.
ISNUMBER returns TRUE if something was found, otherwise FALSE.
-- converts TRUE to 1 and FALSE to 0.
SUM returns sum of above array, if at least one word maths it'll be positive number, otherwise zero.
IFS considers positive number as TRUE, zero as FALSE.
IFS works till first TRUE condition. If F619 found both in first and second ranges it returns result for the first range.
Mmmm does the attached do what you need?
It should, check the text for any of the words in the list, return the 'category' from the column next to it.
This doesn't work with how my data is organised. It is like this:
Big category medium category small categorysmall category
medium category
Big category medium category small categorysmall category
medium category
Big categorty medium categorymedium category
