Search for multiple keywords within a single text cell

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Okay, thanks for the heads-up.
I am attaching a mock excel form showing how I would like the data to appear.
Cheers

In your sample you have comment:

Would like to search column C for keywords like "Highway" or "Planning" or "Operations" and have column D and E automatically update with corresponding industry and focus information:

But the first item has both "Highway" and "Operations" in it... so not sure which one you want?

There are 2 methods I can provide. One to show you how to get a match for first listed word it comes across, and one for getting a match for last listed word it comes across.

First, you need to create a table of values on the side.

So say in M2:M10 you list the possible keywords.
In N2:N10 you would list the corresponding Industries and in O2:O10 you would list the corresponding Focuses.

Then in D2, to get the result for the first keyword it comes across (i.e. "Highway" in C2) use:

Please Login or Register  to view this content.

and copy down and to next column.

To get the result for last keyword (e.g. "Operations" in C2) then.

Please Login or Register  to view this content.

copied down and to next column

Originally Posted by NBVC

To get the result for last keyword (e.g. "Operations" in C2) then.

Please Login or Register  to view this content.

copied down and to next column

I know I'm just repeating something I've said before but in the above case I'd opt for:

Please Login or Register  to view this content.

the 1/ISNUMBER is superfluous

My first post of the day... and I already get jabbed...

Liar - I saw you post in the Water Cooler

I meant real Excel-answering post.

ah, sometimes I can't tell the difference ...
(again: ducking for cover...)

Hi,

Thanks for both of your help. Both functions seem to work fine.

One more quick question: How do I eliminate the #N/A message when the search does not find any keywords within the search string? Can I have it report some other text value instead? I think this would involve the ISERR function, right?

Thanks!

First off - I mistakenly removed your quote re: NBVC's formulae - apologies !

Both LOOKUP functions (NBVC's and my slight adaptation) are based on SEARCH...

The SEARCH function is applied 9 times - once per keyword against C2.

SEARCH will either return a number (found) or an error (not found)

In my example setting the criteria to be a very big number (9.99*10^307) means that given the nature of LOOKUP* the last number returned in the 9 value lookup_vector is the "result".

That number is obviously not much use - so we apply an optional result_vector - which means that rather than returning the number from the lookup_vector the associated item from the result_vector is returned instead (ie the keyword)

NBVC's approach is very similar only it has a couple of additional operations taking place that essentially mean the lookup_vector is populated with either 1 or #DIV/0!
What is important to note is that the criteria value of 2 is still bigger than any numeric value in the lookup_vector and this ensures the last valid result is returned.

Regards error handling - if using XL2007 just use IFERROR


*LOOKUP - the function works as follows:

- returns last item <= criteria from lookup_vector or associated item from result_vector if result_vector specified

- LOOKUP assumes lookup_vector to be sorted in ascending order irrespective of reality

- LOOKUP ignores any values in the lookup_vector not of the same data type as the criteria and this importantly includes error values
(ie an error in the lookup_vector will not cause the LOOKUP to fail)

Given the above it follows then that given the above if the criteria exceeds all known values of the same data type in the lookup_vector then given LOOKUP assumes the vector is sorted it will return the last item of the same data type (or associated value from result_vector where specified)

For the reasons above LOOKUP is used widely to retrieve "last values" - ie last number, last text etc... from a range

The lookup list should be created in ascending hierarchical order...

.. i.e. if Ph.D. Is the highest level achievable then Doctorate/Ph.D. should be listed last in your lookup range, M2:N10 (with no blanks).

The Lookup looks for the last item in the lookup range that matches anything in your B2 entry.

Please post new questions in new threads. Also, no workbook attached.

Auni,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread