Keyword term to search in string

jdash

New Member
Joined
Mar 14, 2011
Messages
18
Hello,

I have two worksheets.

The first has a long list of text strings containing construction related entries like WATER SOFTENERS WITH BRINE TANK or DOMESTIC WATER SOFTENERS for example. There is a unique ID associated with each string in another cell.

The second sheet has a long list of common construction terms, like SOFTENERS for example.

Is there a way to match the term SOFTENERS to each of the two hits on Sheet 1 and return both of their unique IDs?

I have been working with this formula in Sheet 2:
Code:
=IF(A128="","",INDEX(index_con_lookup, MATCH(A128,match_col),3))
Where A128 is the keyword I'm using to search, index_con_lookup is the range with the unique ID (Sheet 1) and match_col is just the column of descriptions from Sheet 1.

But it produces erratic results and changing the MATCH TYPE produces less hits. I've been trying to rearrange the formula various ways but can have it produce accurate results for even the first hit. Subsequent hits I figure I could make a second, third and fourth column that drops out the findings in the column before it. Then I would concatenate all the unique IDs to one cell and that's it.

I have been staring at VB tutorials and codes for hours and I can't make heads or tails of it that way.

Sorry so long. Any thoughts?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,122,817
Messages
5,598,275
Members
414,221
Latest member
MD1222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top