6

Once again I'm struggling to find my answer on Google, but I'm sure it must exist. Sorry if I come across as a novice: I am when it comes to Excel it seems!

What I'd like to be able to do is tell it to search a range, then find cells within that range that contain text that is in my search function. I don't need any kind of result from it other than either TRUE or >1 (if it matches, obviously).

In this instance I'm searching a RANGE that is comprised of years, one year in each cell, and trying to find cells in that range that contain a year from a list of years that are all in one cell.

Basically I'm wanting to use a function similar to the Search function I think.

=SEARCH(text to find, find within text)

However, I'd like it to do the opposite and find cells that contain some of the text within the source cell:

=SEARCH(find within text, text to find)

Or, more specifically

=SEARCH("2001,2002,2003,2004,2005", "2003")

Is this possible without the use of a Macro? I'd prefer to avoid it if at all possible. All cells in question are formatted as Text.

I have been experimenting with COUNTIF, but again it works in the reverse of what I need.

Sorry if this question is unclear. Hope someone can help, thanks in advance.

Joe

JoeP
  • 856
  • 4
  • 15
  • 29

1 Answers1

4

I'm sure there is a better way, but if I'm understanding correctly, you could try SUM in combination with an array formula (enter with Ctrl+Shift+Enter):

=IF(SUM(IFERROR(FIND(A1:E1,G1),0))>0, "FOUND", "NOT FOUND")

Here, A1:E1 contained the individual years and G1 contained the single cell of years. This runs FIND on each cell in the range, returning a position if it finds a match in the target cell and returning 0 if not (IFERROR is a 2007 function - if you don't have Excel 2007, we can rewrite). You then sum the results of the FIND function and if it is greater than 0, it means you found a match somewhere.

enter image description here

RocketDonkey
  • 36,383
  • 7
  • 80
  • 84
  • nice answer! And again this shows how strange the array formula in excel works... – K_B Nov 27 '12 at 15:40
  • @K_B Thanks - arrays definitely make things interesting :) – RocketDonkey Nov 27 '12 at 15:42
  • 3
    You could save a function by using COUNT, i.e. `=IF(COUNT(FIND(A1:E1,G1)),"Found","Not Found")` - that still need's "array entry" - or use my old friend LOOKUP for a non-array version `=IF(ISNA(LOOKUP(2^15,FIND(A1:E1,G1))),"Not Found","Found")` – barry houdini Nov 27 '12 at 19:51
  • @barryhoudini Ah very cool - I didn't know that `COUNT` properly handled `N/A#` values - thanks yet again for teaching me something new :) Awesome `LOOKUP` as well. – RocketDonkey Nov 27 '12 at 20:02
  • 1
    @barryhoudini: your lookup option is excellent. Worked great as I needed to try and avoid arrays. – guitarthrower Jun 02 '16 at 19:01