Click here for important information about

Solved EXCEL: How to find a (sub-)text in a big list

Microsoft Office excel 2007
June 27, 2012 at 01:47:47
Specs: Windows 7
Dear all,
I have two huge vertical lists of various items:

List 1:
red orange
green orange
yellow orange
red pear
green apple

List 2:
orange ; ny
apple ; dc

Now i would like to add a second column to List 1 which contains the text of the second column of List 2 if the word is contained - the List 1 should look at the end like this:

List 1 (final):
red orange ; ny
green orange ; ny
yellow orange ; ny
red pear ; N/A
green apple ; dc

How can I achieve this with a (simple) formula?

Thanks for any hints.

See More: EXCEL: How to find a (sub-)text in a big list

June 27, 2012 at 16:46:31
✔ Best Answer
"Simple" is a relative term and may not be applicable in this case.

In fact, depending on how many variables you have, you may not even be able to do it with a formula.

With your List 1 in A1:A5 and your List 2 in B1:B2, the following formula will give you the results you show in List 1 (final).

You can decide for yourself if it's "simple".

Place this in C1 and drag it down:

=IF(NOT(ISERROR(FIND(LEFT($B$1,FIND(" ",$B$1)-1),A1,1))),
A1&MID($B$1,FIND(" ",$B$1),256),IF(NOT(ISERROR(FIND(LEFT($B$2,
FIND(" ",$B$2)-1),A1,1))),A1&MID($B$2,FIND(" ",$B$2),256),A1&" ; N/A"))

You'll note that it needs a IF segment for each item in List 2. Imagine how long the formula will be for a "huge" List 2. Eventually you will hit the maximum number of characters allowed in a formula. The workaround for that is to put the results of portions of the formula in separate cells then reference those cells in your formula.

Seems to me that a VBA based solution would be a better idea.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 28, 2012 at 12:51:11

thanks. As both of my lists have a couple of thousand respectively a couple of hundred entries (and it is not always "red orange", but sometimes also "mac red orange" or "mac orange red"), I don't think the approach with nested if's is the right solution.

As I don't do VBA, I have now (sadly) resorted to do it manually.

Thanks again for your time.

Report •

June 28, 2012 at 13:36:37
This might be a good time to learn some VBA.

I wrote some code that works for your example data and I don't think that "mac orange red" vs."mac red orange" will be a problem - as long as the suffix is the same for both of those. VBA is a bit more powerful than a formula and can handle that.

Why not post a few more examples of you data and I'l see if my code works?

Please click on the following line and read the instructions on the proper way to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

July 1, 2012 at 11:23:51
Hi DerbyDad03,

yes, I should really learn some VBA. But there is just not enough time to do - business as usual eats up all the time at work, and at home I'm too busy right now with other things.

We are implementing right now a new application/database which will replace the EXCEL version and come with a more flexible reporting and analysis toolset. So I really hope that the indicated "challenge" was a one time problem.

The "manual" resolve only took me three hours...

Thanks again for your time.

Report •

Ask Question