1

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.

Improve this question

1 Answer 1

Reset to default
4

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)))
Improve this answer
4
  • Thanks for replying. I implemented the function, and it works well. Is there any way that this could be edited so that it searches for things in a random order? They only seem to match up if the names in the red list are in the same order as the names appear in the list below. I can work around it, so don't worry too much, but any help would be appreciated.
    – PastaSam
    Oct 5, 2016 at 18:37
  • That is because the data your searching in is in the same column as the match data, if you move it to another column then you reference the whole column as your range not just that small range, ill update with an example
    – Aurielle Perlmann
    Oct 5, 2016 at 18:48
  • move the list of names that are currently next to it, to start in I2, and copy this arrayformula =IFERROR(ARRAYFORMULA(FIND(H2:H, B2:B))) to cell H2
    – Aurielle Perlmann
    Oct 5, 2016 at 18:52
  • Thank you very much, that works. Sorry for the "long" reply time, I've been without internet for a little while. Thank you!
    – PastaSam
    Oct 19, 2016 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.