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.