match multiple partial strings from string in excel

Question

So here I have 2 tables.

ring    layer1  layer2  output
12      45      46      bingo
12      34      75  
13      23      47  
14      23      34      nice_work
14      12      15  
14      45      23  
14      67      89      wow
25      90      124 
67      76      341 


ring    whole_string    value as output
12      23_45_12_78_46  bingo
12      78_89_23_45_90  great
13      23_89_90        awesome
14      45_78_23_45_34  nice_work
14      88_86_85_12     cool
14      67_89_111       wow

what I need is: value as output from tbl2 if
1. tbl1 ring = tbl2 ring
2. tbl1 layer1 & layer2 values must be present in tbl2 whole_string

Can someone help me with excel formula?

Thank you...

I tried using a for loop. It takes a whole lot time.

Can I assume that only one line matches the criteria or is it possible that there are several matches? If several: Which one should be used? — Wolfgang Jacques
– Wolfgang Jacques, Commented Jul 5, 2019 at 9:11
Yes. You can assume that only line matches the criteria because even if there are multiple matches, only one output will be shown - and that is perfectly good with me... — user3471438
– user3471438, Commented Jul 5, 2019 at 9:36
Well, no. If there might be multiple matches, the formula would be different, because it has to be taylored to take the first match only and not produce fake results because of multiple matches. I'll try to build a formula that can handle multiple matches, then. — Wolfgang Jacques
– Wolfgang Jacques, Commented Jul 5, 2019 at 9:51

JvdV · Accepted Answer · 2019-07-05 09:34:38Z

2

You could use:

Formula in D2:

=IFERROR(INDEX($H$1:$H$7,AGGREGATE(14,3,($F$2:$F$7=A2)*(IF(ISNUMBER(SEARCH("_"&B2&"_","_"&$G$2:$G$7&"_")),1,""))*(IF(ISNUMBER(SEARCH("_"&C2&"_","_"&$G$2:$G$7&"_")),1,""))*ROW($F$2:$F$7),1)),"")

Entered as array formula through: Ctrl+Shift+Enter

Drag down...

answered Jul 5, 2019 at 9:34

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3471438 Over a year ago

This is absolutely wonderful! Since I am using this in a macro, how can I work the Ctrl+Shift+Enter?

JvdV Over a year ago

You mean you use it through Evaluate? In that case no need for array, vba recognise it as such.

user3471438 Over a year ago

say.. Range("D2").Formula = "=your formula"......how to work Ctrl+Shift+Enter here?

Collectives™ on Stack Overflow

match multiple partial strings from string in excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related