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?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,025
Messages
5,835,008
Members
430,332
Latest member
Charly_Moon

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