Dynamic Classifications with Excel: LOOKUP + SEARCH

12 Dec 2017 5 minutes

If you like VLOOKUP, HLOOKUP or INDEX + MATCH, then you're going to love LOOKUP + SEARCH.

Warning: This tip is for intermediate Excel users. Do not attempt to read before 08:30 or without coffee in hand.

Are you a returning reader? Skip straight to the Excel download or the Classification formula if you wish.


Classification Problem

A word or group of words within a search phrase may need to be classified. We build out search phrase data models that contain properties of an item. For example a jacket has:

  • Colour (Red, Black, Blue, Green, ...)
  • Materials (Leather, Duffel, ...)
  • Size (Small, Medium, Large, ...)
  • Type (Bomber, Sports, ...)

Those list values can be used for variable substitution.

Search Phrase Model
coats [coats]
jackets [jackets]
blue coat [colour] [coats]
black coat [colour] [coats]
duffle jacket [material] [jackets]
buy duffle jacket [purchasing] [material] [jackets]
small brown winter coat [size] [colour] [season] [coats]

For a keyword analysis of 50,000+ phrases, being able to filter the data in Excel can be tricky using just the 'contains'.


Classification Solution

PPC Campaigns are structured as a tree node, whereby a word group belongs to a campaign. This does not allow for a keyword phrase to appear under multiple nodes of the same data set. Instead, a better solution is to classify words rather than entire search phrases so filters can be applied to important properties.

Lists are a powerful way of dynamically classifying the search phrases. Here are some list examples:

  • Genders (men's, man, women's, woman, ladies)
  • Materials (denim, leather, suede)
  • Occasions (birthday, christmas, easter)
  • Relationships (husband, wife, brother, sister)
  • Seasons (spring, summer, autumn, winter)
  • Timeframes (monday, tuesday, weekend, weekday, january, february)

Tip: If you are working at an agency, ask your clients to send you lists of data, such as categories and product properties.

Ready to work through the classification formula? Here is an example to get you started. Study the formulas in the sheets along with this tutorial.

The combination of a LOOKUP vector with SEARCH allows Excel users to find a group of characters within another group of characters (substring matching).

LOOKUP is not case sensitive, however it is good practice to keep the data uniform. Make sure you TRIM the data to remove any spaces at the beginning or end.

Excel ARRAYs are extremely difficult to use, often cause bugs and very slow, so instead we create a searchable vector.

SEARCH Function

This is done by using the SEARCH function with the premade list as the "find_text" attribute, and the string for the "within_text" attribute that we are trying to lookup. It is important to use absolute cell references.

For example, assuming the keywords we wish to search are on the current tab, in cell G2 the list is on a tab called Genders and the list has 5 items in Column A, the formula would be:


This will not work yet, so expect an #N/A result.


Now wrap that SEARCH inside a LOOKUP, using a "lookup_value" of 2^10, "lookup_vector" is the SEARCH you just performed and the "result_vector" is the same Genders list as in the the SEARCH function, with absolute references.


Error Handling

This formula now show substrings that are within the list and an #N/A value if a match is not found. To make this more friendly, wrap it in an IFERROR statement so that a hyphen is presented if no match is found.


Grouping Items of a List

If your list contains variations of a topic, such as gender, each item will printed individually, i.e. "women's", "woman", "ladies". To collect those variations under a single category of "Women's", add a column in the Genders tab, Column B with the grouping.

Search Phrase Model
Women's Women's
Women Women's
Ladies Women's
Woman Women's
Men's Men's
Men Men's
Man Men's

You can now use VLOOKUP against the value received from LOOKUP + SEARCH to power VLOOKUP. See the tab Keyword List (#2) for examples.

The Final Grouping Classification Formula


You will need to scroll to see the entire code. You now have a dynamically generated classification through substring matching.

Important Notes

The - Hack

If you decide to extend any of the classifications, you will need to insert a new line. Even with absolute cell references, they can often shift from $A$1 to $A$2 if you prepend or append to the list. By using a hyphen at the beginning and end, insert inbetween the hyphens to automatically extend the list, without the need for editing the absolute cell references.

Empty Cells

Empty cells are not allowed in the list, it will break the formula.


This classification will match substrings. This means if you have a word such as "coat", it will match "coat", "coats", "overcoat", but also "coating". This could be a problem if you have substrings with multiple meanings.

Make the List Absolute Cell References Dynamic

This could be done with the use of COUNTA, INDIRECT and CONCATENATION functions. However, this would need to be computed upon each cell unless the value is stored somewhere. This also makes the formula almost unusable and a lot more complicated than just using "The - Hack".

Double Classifications

Some listings may have more than one match. For example, a keyword term could be "black and white dresses", so the first colour "black" would be matched, unless there was a "black and white" classification.

Have any questions? Send me an email or find me on Twitter @ryansiddle.