CODEWITHSUNDEEP
excelif-statementexcel-formula
user13237022
user13237022

Reputation: 3

IF ISNUMBER SEARCH - distinguish between similar text

Hoping someone smarter than myself can help:

Column F contains either of the words below, and I want to excel formula to return whether the data is "White" or "Pink"

=IF(ISNUMBER(SEARCH("Ban",F2)),"White",IF(ISNUMBER(SEARCH("Bandearg",F2)),"Pink")

The problem is that everything returns as "White" as it is finding "Ban" in both. Presume I am using the formula wrong.

Bán is the Irish for White and Bándearg is the Irish for Pink!

Upvotes: 0

Views: 495

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

If those words are all that the cell contains, you can simply use:

=IF(A1="Ban","White",IF(A1="Bandearg","Pink",""))

or, in later versions of Excel (2019+ or O365):

=IFS(A1="Ban","White",A1="Bandearg","Pink",TRUE,"")

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

Maybe just simply add a space behind the criteria and the result value, and your formula become :

=IF(ISNUMBER(SEARCH("Ban ",F2&" ")),"White",IF(ISNUMBER(SEARCH("Bandearg ",F2&" ")),"Pink"))

Upvotes: 0

JvdV
JvdV

Reputation: 75900

As per my comment, simply swap them around:

=IF(ISNUMBER(SEARCH("Bandearg",F2)),"Pink",IF(ISNUMBER(SEARCH("Ban",F2)),"White"))

ISNUMBER checks wheather or not SEARCH returns a number (meaning the substring is found starting at that index/position) or an #VALUE error. In the first case, it will return TRUE, otherwise FALSE continueing with the second nested IF. Note that this formula will return FALSE if "Ban" isn't found at all.

Upvotes: 0

Related Questions