Vlookup using partial text match

Question

I need to create a lookup that will return the class of drug that a certain **** claim belongs to, but the claims contain dose information and my lookup table does not. For example, if I have a cell that says MONTELUKAST TAB 10 MG, I need the lookup to find the entry for MONTELUKAST SODIUM and return the class of drug, in this case LEUKOTRIENE MODIFIERS. The drug name is not always the same number of characters, of course, and there are several drug names. Is there a lookup I can design to do this?

Answer 1

Hi,

You may refer to my solution at this link - http://www.ashishmathur.com/perform-a-lookup-with-inexact-text-strings-andor-spelling-mistakes/

Hope this helps.

Answer 2

Once problem with approximate matches your data might run into is that the column being looked up needs to be sorted. While your data is actually sorted, you are only showing data from rows 786 to 792 and I don't think that all of your data is sorted.

The best way to do this may be a 'helper' column that splits off a keyword that can be used as an exact match. Your Drug Lookup worksheets would look like this.

The formula in D786 is,

=TRIM(LEFT(B786,IFERROR(FIND(CHAR(32),B786),99)))

But you will probably want to start at at D6 and fill down.

The Phar**macy worksheet could reference that column to return the Drug Class from column C.

The formula in G3 is,

=INDEX('Drug Lookup'!$C$6:$C$999, MATCH(TRIM(LEFT(F3, IFERROR(FIND(CHAR(32), F3),99))),'Drug Lookup'!$D$6:$D$999,0))

Addendum: your data looks like it is in a table so the formula might be closer to this,

=INDEX(Table1[[#All],[Drug Class]], MATCH(TRIM(LEFT(F3, IFERROR(FIND(CHAR(32), F3),99))),Table1[[#All],[Helper]],0))

Splitting off a word and relying upon it as the key to a lookup is not always successful but with a little massaging of the data and keyword column (and setting some rules for input) you should be successful.

p.s. how's my typing?

Answer 3

Getting close, but returning the wrong value. It's returning the value one cell down from the one I want. When I enter the formula below, I get "inhaled corticosteroids" instead of the correct value. Here is my formula: =INDEX('Drug Lookup'!C:C,MATCH(F3,'Drug Lookup'!B:B,1)) MONTELUKAST TAB 10 MG is in cell F3 on another worksheet, called pharm-a-cy.

Answer 4

Hi assuming MONTELUKAST TAB 10 MG is in H16, and MONTELUKAST SODIUM in range K16:K18 and the class drug is in  column N same range in another cell enter

=INDEX(L16:L18,MATCH(H16,K16:K18,1))

Answer 5

And I can't tell why Microsoft thinks pharm-a-cy is a bad word. I promise I'm not cussing.