Google Sheets – Using FIND Function in a Range

google sheets

I'm trying to use Google Sheets to make a register-type file. In this file, I have a column of names of people that should be attending and another column where I write the names of the people I have already seen, in separate cells, so I can track other things like time they arrived and food preferences etc. I'm trying to create a FIND statement to search for the names to automatically cross people off the list that have arrived. This is what I've tried:

=FIND(C30, F3:F10)

And it gives the error:

Error

An array value could not be found.

I've managed to make it work by using:

=FIND(C30, F3)

but that literally only searches one cell, which is not helpful at all.

Is there any way I can search a range of field for the phrase in another field? If you want to see an example of what I need, here's a link.

Best Answer

If you also make the argument a range, also the cells your describing in the question do not match the sample sheet you shared - based on the sheet and the cells it is referring to, this is the formula that works:

=IFERROR(ARRAYFORMULA(FIND(B12:B18, B3:B9)))

enter image description here

IFERROR basically avoids showing #N/A when the value is not yet found, and since you want to apply the find function to the list of names you have written you need to pass it a range of names to look up, completed by wrapping it with ARRAYFORMULA .

If you share a more complete sheet that is closer to the actual results your trying to get it would be easier to come up with a more dynamic answer, likely a simpler way to do it also .

To match in any order you should move your search list to another column or sheet , pretend you move your list of names to start in cell I2 you would update your formula and enter it in H2: 

=IFERROR(ARRAYFORMULA(FIND(H2:H, B2:B)))
Related Topic