Vlookup using keywords

[rplohocky]

Hello,
I have a WB with 2 sheets. Sheet1, column "O" is filled with almost identical statements but at the beginning of each statement has a different number pattern. Here's an example.

17849 - 20140469 REPL CLI-UNIT ALREADY TRADED
17849 - 20140237 REPL CLI-UNIT ALREADY TRADED
17849 - 20140193 REPL CLI-UNIT ALREADY TRADED
17849 - 20140519 REPL CLI-UNIT ALREADY TRADED
20170227 116710LS LS0004FX M NO LEASE RATE ATTRIBUTE FOUND
20170210 116710LS LS0004FX M NO LEASE RATE ATTRIBUTE FOUND
LEASE RATE MUST BE FL OR FX
MULTIPLE LEASE RATES, CODE MANUALLY
20170106 116710LS LS0004FX M NO LEASE RATE ATTRIBUTE FOUND
20170106 116710LS LS0004FX L NO LEASE RATE ATTRIBUTE FOUND
20170320 17595LS LS0083FX E NO LEASE RATE ATTRIBUTE FOUND
LEASE RATE MUST BE FL OR FX

As you can see the words are the same but the leading numbers are all different.

In Sheet2 column "A", I have a column that has these same statements but without the numbers and column "B" has "Instructions" on how to fix each of these statements. On Sheet1 column "U" i have a vlookup returning the values found in Sheet2 column "B". This is not working because every statement has different leading numbers. I am trying to find a formula that will look on Sheet2 column "A", for a keyword or string, and then look in Sheet1 column "O" and return the value from Sheet2 column "B".

Keywords or strings I would use to search through the list above.

REPL CLI-UNIT ALREADY TRADED
NO LEASE RATE ATTRIBUTE FOUND
LEASE RATE MUST BE FL OR FX
MULTIPLE LEASE RATES, CODE MANUALLY

Any help would be great! Thanks.

 

[oldbrewer]

can you simplify it by using lines like 22 orange 23 orange and show what is in the lookup table , please?

 

[rplohocky]

can you simplify it by using lines like 22 orange 23 orange and show what is in the lookup table , please?

I need the formula to look at this in column "A" Sheet2.

REPL CLI-UNIT ALREADY TRADED

and then look at this in column "O" Sheet1.

17849 - 20140177 REPL CLI-UNIT ALREADY TRADED

and return the value in in column "B" Sheet2.

This just a basic vlookup formula. The problem is every cell in Sheet1 column "O" has different numbers in front of each string of words so I can't make a basic vlookup work. I just need it to return a value if the vlookup finds the string of words in any of the cells in Sheet1 column "O".

 

[oldbrewer]

I haven't any idea what you want - a very simple example please. ARe you looking for a phrase in a column where there are numbers before the phrases ? Does a phrase occur more than once in the column ?

 

[rplohocky]

I haven't any idea what you want - a very simple example please. ARe you looking for a phrase in a column where there are numbers before the phrases ? Does a phrase occur more than once in the column ?

Yes Sheet 1 column "O" has about 30 different phrases through the column.

I think this is just to hard to explain, I wish I could upload a sample.

 

[philwojo]

When you find the value from Sheet2 on Sheet1, what do you want to return, that is the part I am not clear on can you explain that. Your lookup uses a portion of what is in Sheet1, so are you wanting to return all values from Sheet1 that have the value from Sheet2 or just a single value?

 

[rplohocky]

When you find the value from Sheet2 on Sheet1, what do you want to return, that is the part I am not clear on can you explain that. Your lookup uses a portion of what is in Sheet1, so are you wanting to return all values from Sheet1 that have the value from Sheet2 or just a single value?

Let me explain the report I get.

Each day I get a report that has about 2000 rows of data, columns A thru X.

In column O I have "Error" codes that I have to resolve in our system.

When there is an "error" code, it shows up with different numbers in front of the code.

Each "Error" code has different instructions on how to resolve them.

On Sheet2, I placed the "error" codes (without the leading numbers) into column A and then wrote instructions in column B.

***Original Error code example***
20000801 101028LS LS0002 FX NO LEASE RATE ATTRIBUTE FOUND

***Removed leading numbers***
NO LEASE RATE ATTRIBUTE FOUND

What I need the formula to do is look on Sheet2, column A and go into Sheet1, column O and see if there is a match based on the keyword string and then return the instructions from Sheet2 column B. This formula is just a basic vlookup, look for this and return this, but because every "Error" code that comes in with a different leading number I can't have the vlookup work because they will NOT match identically.

Thanks for the help.

 

[philwojo]

OK, so one last point to clarify, for myself at least, you are saying you can ignore any of the leading numbers and you only care about the "error" text on Sheet1 column O, correct?

 

[rplohocky]

OK, so one last point to clarify, for myself at least, you are saying you can ignore any of the leading numbers and you only care about the "error" text on Sheet1 column O, correct?

Correct!