I have a spreadsheet with two tabs: one is a search term data from an Adwords campaign and another one is a list of text fragments.

I want to use a function to return check if the search terms contain

Search terms tab

|         Search term         | Clicks | Cost |
| nike running shoes          |     50 | $31  |
| soccer cleats               |     30 | $40  |
| lace up boots               |     40 | $45  |
| spikeless adidas golf shoes |     20 | $15  |
| red/blue converse classics  |     15 | $20  |
| flyknit nike for men        |     25 | $30  |

Text fragments tab


I want to add a column to the first tab to output the matched value from the text fragment tab next to each search term.

the output would look like this:

|         Search term         | Clicks | Cost | matched term |
| nike running shoes          |     50 | $31  | nike         |
| soccer cleats               |     30 | $40  |              |
| lace up boots               |     40 | $45  |              |
| spikeless adidas golf shoes |     20 | $15  | adidas       |
| red/blue converse classics  |     15 | $20  | converse     |
| flyknit nike for men        |     25 | $30  | nike         |

I tried to use the SEARCH function on a range: =SEARCH(tab2!A1:A63,A2) but I get #VALUE!


I suggest a following solution based on how I understand your question. In this example the Search Term is in sheet named tab1!C3:F8. The fragment text strings are in tab2!C3:C5.

Now in F3 put the following formula and from within the Formula Bar Press CTRL + SHIFT + ENTER to create an array formula. The formula shall be enclosed in curcly braces to indicate that it's an array formula and drag it down along the length of the table. For this to work, you should leave one cell free above the list of fragmented string table i.e. start your fragmented list in row 2 onward in tab2 sheet


enter image description here

enter image description here

enter image description here


If you are using Google Spreadsheets, the same formula works fine too. Pressing CTRL + SHIFT + ENTER wraps the formula in a function name ArrayFormula. See the below screenshot.

enter image description here

  • any idea how to make an array formula in google sheet / excel for mac? Also, what is the impact of 9^99 in your formula? – Bogdan Nov 26 '17 at 16:08
  • Check support.google.com/docs/answer/3093275?hl=en for Google Spreadsheets – patkim Nov 26 '17 at 18:55
  • The formula searches for presence of all fragment list one by one in single cell of main table. If a match is found the row number of fragment cell is returned, if not return a large number (e.g. 9^99) way beyond the max row numbers in Excel. Min of that array is the row number of fragment string matched. Manipulate by subtracting the row number of first cell before the list, you get an Index position of fragment string number starting from number 1. Now apply Index function and return the matched string from the list and put it in Column F in main table. – patkim Nov 26 '17 at 19:01
  • From the Internet search - Excel for Mac - Array formula - CONTROL + U and then press ⌘ + RETURN and for Mac Excel 2016 CTRL + SHIFT + RETURN. However I have not verified as I don't have Mac. – patkim Nov 26 '17 at 19:04

With Sheet1 containing the search terms and Sheet2 containing the fragments, consider the following User Defined Function:

Public Function GetKeyWord(s As String, rng As Range) As String
    Dim s2 As String, r As Range
    s2 = LCase(" " & s & " ")

    GetKeyWord = ""

    For Each r In rng
        If InStr(1, s2, " " & r.Value & " ") > 0 Then
            GetKeyWord = r.Value
            Exit Function
        End If
    Next r
End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:


To learn more about macros in general, see:




and for specifics on UDFs, see:


Macros must be enabled for this to work!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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