Search keyword in list with partial keyword found

[Fazniro]

Hi everyone I have this problem at work with a massive list of keywords that i need to find and match to another list.Creating the right formula will save me like 10 hours of work, so i would be extremely thankful for a solution.I have 2 list of keywordsbut i can only find part of the keyword in the other list.Objective: Create formula to search for key in the "key master" column and return the description1 if column B says include aka. "1", if not look at column D, if this one says include 1 then use description2.Please see the following google sheet: https://docs.google.com/spreadsheets/d/1uNSKpV5IfbfqfwLLSKQmchyjsJKpIWCELptqYkOc2YE/edit?usp=sharing I have also pasted the same sample here.Key master Include/skip Description1 Include/skip Description2lpd02050303 1 Shoe 1 Nikelpd02040309 0 Hat 1 Coca Colalpd09250101 1 T-shirt 1 Ralph Lauren Key Value <-- (results) lpd0201 No value found <-- (results) lpd0205 Shoe <-- (results) lpd0204 Coca Cola <-- (results) lpd0925 T-Shirt <-- (results) best,Jakob

 

[Marcelo Branco]

Maybe this

J2 copied down
=IFERROR(INDEX($B$2:$E$4,MATCH(I2&"*",$A$2:$A$4,0),1+MATCH(1,INDEX($B$2:$E$4,MATCH(I2&"*",$A$2:$A$4,0),0),0)),"Not found")

Hope this helps

M.

 

[Fazniro]

For some reason something decided to remove all my line breaks.

Please see this link:
https://docs.google.com/spreadsheets/d/1uNSKpV5IfbfqfwLLSKQmchyjsJKpIWCELptqYkOc2YE/edit#gid=0

Will try reformulate the problem here as the first part make much sense in its current setting and I am not allowed to re-post the original message.

I need to find keyword from list A
inside the list B using the keyword from A, but the list B has a longer keyword.

List A:
Key value
abc123 =search(... in list B for abc123.. return value in the column next to it).... result= chocolate
abc232 result = milk

List B:
master key description1.
abc1230101 chocolate
abc1880101 candy
abc2320101 milk

i need the formula in column B.


best regards
Jakob

 

[Joe4]

For some reason something decided to remove all my line breaks.

It might depend on the device you are posting from. Were you posting from a phone?

Your last post looks fine, so what did you do differently?

 

[Fazniro]

Maybe this

J2 copied down
=IFERROR(INDEX($B$2:$E$4,MATCH(I2&"*",$A$2:$A$4,0),1+MATCH(1,INDEX($B$2:$E$4,MATCH(I2&"*",$A$2:$A$4,0),0),0)),"Not found")

Hope this helps

M.

thanks a lot ! got it working, now Im just trying to understsand the formula but thanks a lot ! really appreciate it.

any way i can close this thread?

 

[Fazniro]

It might depend on the device you are posting from. Were you posting from a phone?

Your last post looks fine, so what did you do differently?

i posted via advanced, the second time. and then clicked preview post then it worked.

 

[Joe4]

i posted via advanced, the second time. and then clicked preview post then it worked.

Then that is how I would recommend posting. There might be something with that Editor and your environment that don't play nice.

any way i can close this thread?

No, we intentionally do not do that, as many times there are multiple ways to answer the same questions, and we do not want to discourage other replies (many times the later ones are ever better than the first ones).

If your issues is solved, you can just reply saying as much.

 

[Marcelo Branco]

thanks a lot ! got it working, now Im just trying to understsand the formula but thanks a lot ! really appreciate it.

You are welcome. Glad to help.

M.

 

[Fazniro]

You are welcome. Glad to help.

M.

Hi Marcelo,

Any chance i could get you to do a simplified version of the formula, just so i can understand it better?
Can you take out the "check for include/skip" in the formula? because I'm struggling to get it working now in my own real file + sometimes i just need the function to lookup part of a word in another list, but i cant extract the logic behind your (in my world) very advanced formula

 

[Marcelo Branco]

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Key master​

[/TD]
[TD]

Include/skip​

[/TD]
[TD]

Description1​

[/TD]
[TD]

Include/skip​

[/TD]
[TD]

Description2​

[/TD]
[TD]

include/skip​

[/TD]
[TD]

description3​

[/TD]
[TD][/TD]
[TD]

Key​

[/TD]
[TD]

Value​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

lpd020101​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

Shoe​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

Nike​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

air max​

[/TD]
[TD][/TD]
[TD]

lpd0201​

[/TD]
[TD]

Shoe​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

lpd020501​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

Hat​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

Coca Cola​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

zero​

[/TD]
[TD][/TD]
[TD]

lpd0205​

[/TD]
[TD]

Coca Cola​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

lpd020402​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

T-shirt​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

Ralph Lauren​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

red​

[/TD]
[TD][/TD]
[TD]

lpd0204​

[/TD]
[TD]

T-shirt​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

lpd03301​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

blank​

[/TD]
[TD="bgcolor: #D9D9D9"]

0​

[/TD]
[TD="bgcolor: #D9D9D9"]

blank​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

iron​

[/TD]
[TD][/TD]
[TD]

lpd03301​

[/TD]
[TD]

iron​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2 copied down
=IFERROR(INDEX($B$2:$G$5,MATCH(I2&"*",$A$2:$A$5,0),1+MATCH(1,INDEX($B$2:$G$5,MATCH(I2&"*",$A$2:$A$5,0),0),0)),"Not found")

This isn't a very complicated formula.
It's a INDEX/MATCH type formula that locates a cell (row/column) in range B2:G5 (gray area)
1) locates the row in column A whose value begins with the value in column I.
Note that i used a wildcard (*) , so the lookup_values becomes the value in column I concatenated with an asterisk. For example, in J2, the lookup_value = lpd0201*

2) the first column, in this row, that contains 1

To see how it works, step by step, select a cell that contains a formula and go to
Formulas > Evaluate formula

If you're not familiar with INDEX/MATCH take a look at
http://www.contextures.com/xlFunctions03.html

M.