Invalid Data Type using Find function

Jamie Null

Hi All,

I can't seem to find the error in my formula.  I'm trying to use FIND() to locate a unique id # SE2 within a column on another spreadsheet.  The column in the other sheet is a text column.

=FIND("SE2", {**2019 Event Builder Range 1})

I keep getting back Invalid Data Type.  I'm not sure what I'm doing wrong.  Can anyone shed some light on the potential problem?  This is my first time trying to use FIND() and I'm not having much success.

Paul Newcome

The FIND function is designed to look at a specific text string. You can't use an entire range as the "text to search".

 

If all you need is to determine if it is found within an entire column, you would use a JOIN function to pull all of those values within your range together into a text string for the FIND function to evaluate.

 

=FIND("SE2", JOIN({**2019 Event Builder Range 1}))

 

This will return a number. If the return is 0, then it is not found within the column. If it is greater than 0, then it is found. Unless you have consistency in data, you will need to use some other means to tell you the location within the range that the unique ID was found.

 

A MATCH/JOIN/COLLECT/FIND would work for finding the location. Something along the lines of...

 

=MATCH(JOIN(COLLECT({**2019 Event Builder Range 1}, {**2019 Event Builder Range 1}, FIND("SE2", @cell) > 0)), {**2019 Event Builder Range 1}, 0)

 

This will give you a number based on the cell's location within the range. If you are only looking at one column, then it should coincide with the row number within the range.

 

This will only work if there is no possibility of that unique ID being found in more than 1 cell.

 

If none of these work for you, let us know, and we will try to help with a solution. 

Jamie Null

Thank you that was very helpful.  It's working!

If I want the formula to only come back with "Yes" if the referenced column in another sheet contains "COO/CFO".  Do you have any recommendations for how to do that? Best formulas to use?  I was playing with IF() but I must be missing a step because I'm receiving the unparseable error.

 

=IF({DEMO 2019 Sponsorship Grid Range 2}="COO/CFO, FIND([Column7]2, JOIN({DEMO 2019 Sponsorship Grid Range 4})), "Yes", "No")

 

Paul Newcome

If you are looking for COO or CFO to show up one time anywhere within the column, you would use...

 

=IF(OR(FIND("COO", JOIN({Cross Sheet Range})) > 0, FIND("CFO", JOIN({Cross Sheet Range})) > 0), "Yes", "No")

 

Are you looking for a single occurrence within the range no matter where it is, or are you looking for something more specific?

Jamie Null

I really appreciate the help.

I have 2 conditions I'm trying to meet.  The first one is if our sponsorship grid (referenced sheet) contains a certain event name "COO/CFO" (this is the event name).  And another column in the same referenced sheet contains  "SE2" then display "Yes", otherwise display "No"

I initially thought something like this might work, but I must be missing something.

=IF(AND({DEMO 2019 Sponsorship Grid Range 2} = "COO/CFO", FIND("SE2", JOIN({DEMO 2019 Sponsorship Grid Range 4})) > 0, FIND([Column7]1, JOIN({DEMO 2019 Sponsorship Grid Range 2})) > 0), "Yes", "No")

Paul Newcome

Ah. Ok. It would be a simple combination of the two formulas already provided...

 

=IF(AND(FIND("COO/CFO", JOIN({Range 1})) > 0, FIND("SE2", JOIN({Range 2})) > 0), "Yes", "No")

 

Of course you will need to set the appropriate ranges, but this is saying that if it finds "COO/CFO" anywhere withing the first range and "SE2" anywhere within the second range it will display a "Yes", if not it will display a "No".

Jamie Null

You are amazing!  Thank you so much for your help!

 

Paul Newcome

Happy to help! 

 

It does seem though that you are going through a lot of effort to find a very specific occurrence in a single sheet. Is it possible that there may be a more efficient way to accomplish your overall goal? If you would like, feel free to post screenshots and more detail regarding the overall process and workflow, and I would be happy to help further if you are interested.