VLOOKUP using text

[mdt1972]

Hey Everyone,


I'm sure what I'm thinking of is possible, but I am struggling with using VLOOKUP within a text string. Below in my example I need to compare the test name in column C to the test name in Column A. When a match is found I need it to fill in the corresponding numeric test code in column B into Column D. The problem I am having is that the names are not always exact. For example Lab 1 may call the test "Heavy Metals Profile Urine" and Lab 2 may call the same test just "Heavy Metals". Is there a way to match on partial words or begining words in a formula where there is a text string? or am I thinking in the wrong direction. Any guidance would be greatly appreciated
[TABLE="width: 740"]
<TBODY>[TR]
[TD]Lab Test Name 1</SPAN>[/TD]
[TD]Lab Test Code 1</SPAN>[/TD]
[TD]Lab Test Name 2</SPAN>[/TD]
[TD]Lab Test Code Match</SPAN>[/TD]
[/TR]
[TR]
[TD]HLA B5701 Test</SPAN>[/TD]
[TD]6790</SPAN>[/TD]
[TD]Cholinesterase, Serum</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cholinesterase</SPAN>[/TD]
[TD]24365</SPAN>[/TD]
[TD]HLA</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Heavy Metals Profile, Urine</SPAN>[/TD]
[TD]2345</SPAN>[/TD]
[TD]Alkaline Phosphatase</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CMV</SPAN>[/TD]
[TD]2345</SPAN>[/TD]
[TD]Alkaline Phosphatase Bone</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Lead</SPAN>[/TD]
[TD]86478</SPAN>[/TD]
[TD]Lead, Blood</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Selenium, Urine</SPAN>[/TD]
[TD]2145</SPAN>[/TD]
[TD]Albumin, CSF</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tricyclic Antidepressants</SPAN>[/TD]
[TD]345</SPAN>[/TD]
[TD]Heavy Metals </SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Albumin CSF</SPAN>[/TD]
[TD]967865</SPAN>[/TD]
[TD]Selenium</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alkaline Phos</SPAN>[/TD]
[TD]12345</SPAN>[/TD]
[TD]CMV Antibodies</SPAN>[/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Declamatory]

I'm not sure if this will work for you but try the following.

Name the data in your first two columns "testcode1"

Then use the formula in your Lab Test Code Match column (I assume that is column D) =IFERROR(VLOOKUP(C2&"*",testcode1,2,FALSE),"")

 

[steve the fish]

Really you should clean the data by standardising your naming of products or even better don't use names at all, use codes for ID purposes.