CODEWITHSUNDEEP
excelexcel-formula
Tock
Tock

Reputation: 61

Excel Find/Search Function Returning False Negatives

This simple issue has me absolutely stumped. I'm trying to search a single cell for a list of items, and for some reason it is only working part of the time.

enter image description here

Columns A:C are all formatted as Text, and there are no trailing/leading spaces in Column A.

Replacing search with find returns the same thing, as does using a named range instead of A2:A4.

Searching for a single/specific item (=SEARCH($A$3,B5)) works just fine.

Upvotes: 0

Views: 623

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Your formula is returning an array of values, which you can see if you use the Formula Evaluation tool on the Formula bar. On the worksheet, you will see only the first array element.

Spread out, the return looks like:

enter image description here

Note that you only see the "number" if it is first in the array (eg Yellow)

If all you want is a TRUE/FALSE answer, then you need to remove the errors and OR the result:

=OR(ISNUMBER(SEARCH($A$2:A$4,B2)))

This is an array formula. In some versions of Excel you may need to enter/confirm an array formula by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

enter image description here

If you want something else, please clarify.

Edit: OP has now clarified that what he really wants is to return the color. Assuming there is only a single color to be returned, the following will return the first color:

    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,1/(ISNUMBER(SEARCH($A$2:A$4,B2)))*ROW($A$2:$A$4),1)),"")

enter image description here

In the case where there are multiple colors, and you want to return them all, it would be best if you have Excel 2016+ with the TEXTJOIN function. If you do, you can use something like:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX($A:$A,AGGREGATE(15,6,1/(ISNUMBER(SEARCH($A$2:A$4,B2)))*ROW($A$2:$A$4),{1,2,3})),""))

In the formula above, the array constant {1,2,3} refers to the number of possible entries in find_text. If this number is much more than three, or will vary, replace the array constant with a formula to generate the relevant array.

One such formula might be:

ROW($A$1:INDEX($A:$A,ROWS(find_text)))

The entire formula might then be:

=TEXTJOIN(", ",TRUE,IFERROR(INDEX($A:$A,AGGREGATE(15,6,1/(ISNUMBER(SEARCH(find_text,B2)))*ROW(find_text),ROW($A$1:INDEX($A:$A,ROWS(find_text))))),""))

enter image description here

Upvotes: 2

bosco_yip
bosco_yip

Reputation: 3802

1] Find_text position

In C2, formula copied down :

=LOOKUP(9^9,SEARCH(A$2:A$4,B2))

2] Find_text result

In D2, formula copied down :

=LOOKUP(9^9,SEARCH(A$2:A$4,B2),A$2:A$4)

enter image description here

Upvotes: 0

Related Questions