• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FIND( ) applied to a column

I have a situation where I have a lookup value and I want to see if that value appears anywhere within a list (as a complete match or even a partial match). In the attached file, I am using the FIND( ) on a lookup value (41346.YR1.0002) and I want to see if that value appears anywhere within a list (column I).

For example, 41346.YR1.0002 does appear in I9 (as a complete match) and also in cells I10:I13 as a partial match which is ok.

My formula is in L7, but apparently the FIND( ) can't handle arrays.

Is there a function that can do this?
 

Attachments

  • Chandoo.org - FIND function in a column.xlsx
    10.1 KB · Views: 4
Seems to work fine here;

82815

The numbers in the red boxes indicate a find (and position).
I added a value prefixed with zzzz to ensure a find anywhere in the string.
If you want to see True/False instead of numbers, change that formula in cell L7 to:
=ISNUMBER(FIND(F7,$I$7:$I$17,1))

If you want a single celled result, showing TRUE if the lookup value is anywhere in the list, but you don't need to know where, then try:
=SUM(IFERROR(FIND(F7,$I$7:$I$17),0))>0
 
Last edited:
I thought I understood the FILTER( ) but I guess not. In the attached file, I thought I should only get what is in the yellow cell. Since D5 does not equal E6:E11, I would have expected the #CALC! error in H6:H11.

What is happening here?
 

Attachments

  • Chandoo.org - FILTER( ).xlsx
    10.1 KB · Views: 5
Your formula returns every member of the lookup list that is a match for the lookup value. There is only one, so the formula returns a single value (in fact it is still an array, but only contains one term). You have then dragged the formula down and, unsurprisingly, it returns the same result from the same formula every time.
Code:
= FILTER(lookupList, lookupList=lookupValue)
returns the first value in cell E5 whereas
Code:
= FILTER(lookupList,
    ISNUMBER(FIND(lookupValue, lookupList))
  )
will return E5:E9 since they each contain "A7077.OY3" as a substring.
 

Attachments

  • Chandoo.org - FILTER( ).xlsx
    10.1 KB · Views: 1
Back
Top