Lookup data in a column cell using an Array, return specific value to new column

[notanexpertinexcel]

I need a formula that will search sheet1 columnB for specific text from sheet 2 columns B through column AZ. If Match, return a the value from sheet2 columnA and place in sheet 1 columnC, second match to columnD, third to ColumnE, etc.

The text in sheet 2 that I'm searching for can be found anywhere within sheet1 column b, and there can be partial matches. I tried using a combination of Search and Match, but it's not working. And the text can be found more than once as it can belong to multiple rows in sheet 2.

=

Example:

Sheet 1 (my list of data that I'm searching)

ColumnA ColumnB ColumnC ColumnD ColumnE

MIGRAT2703	ANKLE LIMITED AP,LAT RIGHT
MIGRAT3624	ANKLE LIMITED AP,LAT RIGHT
MIGRAT3252	AORTA
MIGRAT2284	AORTOGRAM ABD BIL ILIOFEM Sand I Abdomen	Arthrogram
MIGRAT3626	AORTOGRAM ABD W SERIAL S Abdomen
MIGRAT2285	AORTOGRAM ABD W SERIAL Sand I
MIGRAT2644	AORTOGRAM ABD W SERIAL Sand I
MIGRAT2707	AORTOGRAM ABD W SERIAL Sand I
MIGRAT3589	AORTOGRAM ABD W SERIAL Sand I
MIGRAT3627	AORTOGRAM ABD W SERIAL Sand I
MIGRAT3854	AORTOGRAM ABD W SERIAL Sand I
MIGRAT2286	AORTOGRAM THORAC W SRIAL S I
MIGRAT3628	AORTOGRAM THORAC W SRIAL S I
MIGRAT3253	ART DUPLEX LOW EXT UNLAT LEFT
MIGRAT3320	ARTH SHOULDER RIGHT, CT ANGIOGRAPHY ABD L
MIGRAT31	ARTHROGRAM ANKLE LEFT
MIGRAT797	ARTHROGRAM ANKLE RIGHT
MIGRAT32	ARTHROGRAM ELBOW INJ W MRI LEFT
MIGRAT798	ARTHROGRAM ELBOW INJ W MRI LEFT
MIGRAT2708	ARTHROGRAM ELBOW INJ W MRI LEFT
MIGRAT33	ARTHROGRAM ELBOW INJ W MRI RIGHT
MIGRAT799	ARTHROGRAM ELBOW INJ W MRI RIGHT
MIGRAT2709	ARTHROGRAM ELBOW INJ W MRI RIGHT
MIGRAT3145	ARTHROGRAM ELBOW INJ W MRI RIGHT
MIGRAT34	ARTHROGRAM ELBOW LEFT
MIGRAT2710	ARTHROGRAM ELBOW LEFT
MIGRAT35	ARTHROGRAM ELBOW RIGHT
MIGRAT800	ARTHROGRAM ELBOW RIGHT
MIGRAT2711	ARTHROGRAM ELBOW RIGHT
MIGRAT2287	ARTHROGRAM HIP
MIGRAT2288	ARTHROGRAM HIP INJ W MRI
MIGRAT3629	ARTHROGRAM HIP INJ W MRI
MIGRAT801	ARTHROGRAM HIP INJ W MRI LEFT
MIGRAT36	ARTHROGRAM HIP INJ W MRI RIGHT
MIGRAT3146	ARTHROGRAM HIP INJ W MRI RIGHT
MIGRAT802	ARTHROGRAM HIP LEFT
MIGRAT2289	ARTHROGRAM HIP LEFT
MIGRAT2712	ARTHROGRAM HIP LEFT
MIGRAT37	ARTHROGRAM HIP RIGHT

<tbody>
</tbody>

Sheet 2 (My lookup values)

Abdomen	abd	adrenal	renal	visceral	aortagram	arterial embolization	kidney
Arthrograms	arthrogram elbow	arthrogram knee	arthrogram hip	arthrogram shoulder	arthrogram TMJ	arthrogram SI	arthrogram wrist

<tbody>
</tbody>

Extremities Lower	lower extrem	ankle	toes	foot	femur	tibia
Extremities Upper	upper extrem	clavicle	upper ext	elbow	shoulder	finger
Extremity Lower Distal	iliac	ankle	toes	foot	heel	lower ext
Extremity Lower Proximal	angiogram ext	angioplasty illiac	angioplasty peripheral	arad knee	arteriogram ext	arthrogram knee
Extremity Upper Distal	finger	wrist	hand	elbow	upper ext	forearm
Extremity Upper Proximal	shoulder	humerus	clavicle	scapula

<tbody>
</tbody>

ALSO, not sure if someone has advanced knowledge, but my ultimate goal is that if there are more than 1 match (so that there is data in column D in sheet 1, that it would copy the existing row into another row and add that 2nd match in column C (instead of column D). I will easily be able to upload into my receiving system otherwise, i will have to update it manually for 1000's of rows.

NOTE: I tried to use the Mr.ExcelHTML but it is freezing up Excel 2013, windows 7.

 

[HockeyDiablo]

I think this is a simpler solution than you think. I am a little fuzzy on your directions. Could you provide the desired outcome on perhaps 5 or so rows? I would like to see what you truly need,

cheers

 

[notanexpertinexcel]

Here's an example on a couple.

MIGRAT32	ARTHROGRAM ELBOW INJ W MRI LEFT	emptycell
MIGRAT23	CT ABDOMEN W O CONTRAST	emptycell

<colgroup><col><col><col></colgroup><tbody>
</tbody>

BECOMES

MIGRAT32	ARTHROGRAM ELBOW INJ W MRI LEFT	Arthrograms
MIGRAT32	ARTHROGRAM ELBOW INJ W MRI LEFT	Extremities Upper
MIGRAT32	ARTHROGRAM ELBOW INJ W MRI LEFT	Extremity Upper Distal

<colgroup><col><col><col></colgroup><tbody>
</tbody>

MIGRAT23	CT ABDOMEN W O CONTRAST	Abdomen

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Matched search terms are highlighted in Red. Returned values are in Blue.

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Abdomen

abd

adrenal

renal

visceral

<tbody>
</tbody>

Arthrograms

arthrogram elbow

arthrogram knee

arthrogram hip

arthrogram shoulder

<tbody>
</tbody>

Extremities Upper	upper extrem	clavicle	upper ext	elbow	shoulder	finger
Extremity Lower Distal	iliac	ankle	toes	foot	heel	lower ext
Extremity Lower Proximal	angiogram ext	angioplasty illiac	angioplasty peripheral	arad knee	arteriogram ext	arthrogram knee
Extremity Upper Distal	finger	wrist	hand	elbow	upper ext	forearm

<tbody>
</tbody>

Thank you for your help!

 

[notanexpertinexcel]

No worries on this. I did this manually, as there was a time deadline. I appreciate all the advice. Was hoping that I could put together a small .mdb or similar that I could easily add rows and it would automatically file itself according to the synonyms.

Thanks again!

K