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
[TABLE="width: 879"]
<tbody>[TR]
[TD]MIGRAT2703[/TD]
[TD]ANKLE LIMITED AP,LAT RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3624[/TD]
[TD]ANKLE LIMITED AP,LAT RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3252[/TD]
[TD]AORTA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2284[/TD]
[TD]AORTOGRAM ABD BIL ILIOFEM Sand I Abdomen[/TD]
[TD]Arthrogram[/TD]
[/TR]
[TR]
[TD]MIGRAT3626[/TD]
[TD]AORTOGRAM ABD W SERIAL S Abdomen[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2285[/TD]
[TD]AORTOGRAM ABD W SERIAL Sand I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2644[/TD]
[TD]AORTOGRAM ABD W SERIAL Sand I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2707[/TD]
[TD]AORTOGRAM ABD W SERIAL Sand I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3589[/TD]
[TD]AORTOGRAM ABD W SERIAL Sand I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3627[/TD]
[TD]AORTOGRAM ABD W SERIAL Sand I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3854[/TD]
[TD]AORTOGRAM ABD W SERIAL Sand I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2286[/TD]
[TD]AORTOGRAM THORAC W SRIAL S I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3628[/TD]
[TD]AORTOGRAM THORAC W SRIAL S I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3253[/TD]
[TD]ART DUPLEX LOW EXT UNLAT LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3320[/TD]
[TD]ARTH SHOULDER RIGHT, CT ANGIOGRAPHY ABD L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT31[/TD]
[TD]ARTHROGRAM ANKLE LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT797[/TD]
[TD]ARTHROGRAM ANKLE RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT32[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT798[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2708[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT33[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT799[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2709[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3145[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT34[/TD]
[TD]ARTHROGRAM ELBOW LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2710[/TD]
[TD]ARTHROGRAM ELBOW LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT35[/TD]
[TD]ARTHROGRAM ELBOW RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT800[/TD]
[TD]ARTHROGRAM ELBOW RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2711[/TD]
[TD]ARTHROGRAM ELBOW RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2287[/TD]
[TD]ARTHROGRAM HIP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2288[/TD]
[TD]ARTHROGRAM HIP INJ W MRI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3629[/TD]
[TD]ARTHROGRAM HIP INJ W MRI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT801[/TD]
[TD]ARTHROGRAM HIP INJ W MRI LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT36[/TD]
[TD]ARTHROGRAM HIP INJ W MRI RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT3146[/TD]
[TD]ARTHROGRAM HIP INJ W MRI RIGHT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT802[/TD]
[TD]ARTHROGRAM HIP LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2289[/TD]
[TD]ARTHROGRAM HIP LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT2712[/TD]
[TD]ARTHROGRAM HIP LEFT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT37[/TD]
[TD]ARTHROGRAM HIP RIGHT[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (My lookup values)

[TABLE="width: 646"]
<tbody>[TR]
[TD]Abdomen[/TD]
[TD]abd[/TD]
[TD]adrenal[/TD]
[TD]renal[/TD]
[TD]visceral[/TD]
[TD]aortagram[/TD]
[TD]arterial embolization[/TD]
[TD]kidney[/TD]
[/TR]
[TR]
[TD]Arthrograms[/TD]
[TD]arthrogram elbow[/TD]
[TD]arthrogram knee[/TD]
[TD]arthrogram hip[/TD]
[TD]arthrogram shoulder[/TD]
[TD]arthrogram TMJ[/TD]
[TD]arthrogram SI[/TD]
[TD]arthrogram wrist[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 582"]
<tbody>[TR]
[TD]Extremities Lower[/TD]
[TD]lower extrem[/TD]
[TD]ankle[/TD]
[TD]toes[/TD]
[TD]foot[/TD]
[TD]femur[/TD]
[TD]tibia[/TD]
[/TR]
[TR]
[TD]Extremities Upper[/TD]
[TD]upper extrem[/TD]
[TD]clavicle[/TD]
[TD]upper ext[/TD]
[TD]elbow[/TD]
[TD]shoulder[/TD]
[TD]finger[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Extremity Lower Distal[/TD]
[TD]iliac[/TD]
[TD]ankle[/TD]
[TD]toes[/TD]
[TD]foot[/TD]
[TD]heel[/TD]
[TD]lower ext[/TD]
[/TR]
[TR]
[TD]Extremity Lower Proximal[/TD]
[TD]angiogram ext[/TD]
[TD]angioplasty illiac[/TD]
[TD]angioplasty peripheral[/TD]
[TD]arad knee[/TD]
[TD]arteriogram ext[/TD]
[TD]arthrogram knee[/TD]
[/TR]
[TR]
[TD]Extremity Upper Distal[/TD]
[TD]finger[/TD]
[TD]wrist[/TD]
[TD]hand[/TD]
[TD]elbow[/TD]
[TD]upper ext[/TD]
[TD]forearm[/TD]
[/TR]
[TR]
[TD]Extremity Upper Proximal[/TD]
[TD]shoulder[/TD]
[TD]humerus[/TD]
[TD]clavicle[/TD]
[TD]scapula[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 544"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]MIGRAT32[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD]emptycell[/TD]
[/TR]
[TR]
[TD]MIGRAT23[/TD]
[TD]CT ABDOMEN W O CONTRAST[/TD]
[TD]emptycell[/TD]
[/TR]
</tbody>[/TABLE]

BECOMES

[TABLE="width: 544"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]MIGRAT32[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD]Arthrograms[/TD]
[/TR]
[TR]
[TD]MIGRAT32[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD]Extremities Upper[/TD]
[/TR]
[TR]
[TD]MIGRAT32[/TD]
[TD]ARTHROGRAM ELBOW INJ W MRI LEFT[/TD]
[TD]Extremity Upper Distal[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 544"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MIGRAT23[/TD]
[TD]CT ABDOMEN W O CONTRAST[/TD]
[TD]Abdomen[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 879"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]
Matched search terms are highlighted in Red.

Returned values are in Blue.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 629"]
<tbody>[TR]
[TD="width: 169"]Abdomen[/TD]
[TD="width: 132"]abd[/TD]
[TD="width: 106"]adrenal[/TD]
[TD="width: 129"]renal[/TD]
[TD="width: 93"]visceral[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 629"]
<tbody>[TR]
[TD="width: 169"]Arthrograms[/TD]
[TD="width: 132"]arthrogram elbow[/TD]
[TD="width: 106"]arthrogram knee[/TD]
[TD="width: 129"]arthrogram hip[/TD]
[TD="width: 93"]arthrogram shoulder[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: cms_table, width: 582"]
<tbody>[TR]
[TD]Extremities Upper[/TD]
[TD]upper extrem[/TD]
[TD]clavicle[/TD]
[TD]upper ext[/TD]
[TD]elbow[/TD]
[TD]shoulder[/TD]
[TD]finger[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Extremity Lower Distal[/TD]
[TD]iliac[/TD]
[TD]ankle[/TD]
[TD]toes[/TD]
[TD]foot[/TD]
[TD]heel[/TD]
[TD]lower ext[/TD]
[/TR]
[TR]
[TD]Extremity Lower Proximal[/TD]
[TD]angiogram ext[/TD]
[TD]angioplasty illiac[/TD]
[TD]angioplasty peripheral[/TD]
[TD]arad knee[/TD]
[TD]arteriogram ext[/TD]
[TD]arthrogram knee[/TD]
[/TR]
[TR]
[TD]Extremity Upper Distal[/TD]
[TD]finger[/TD]
[TD]wrist[/TD]
[TD]hand[/TD]
[TD]elbow[/TD]
[TD]upper ext[/TD]
[TD]forearm[/TD]
[/TR]
</tbody>[/TABLE]




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