VLOOKUP on partial text in a string

[raheem]

I want to detect a part of string from a cell and then apply vlookup to get corresponding value from another table. Please refer below tables. Values are to be extracted from second table into first table: (there are also few spaces in first table in start of string)

First Table

Structure	Value
** WBS 99OP-11223.P.01 Project RT
* WBS 99OP-11223.P.01.01 Project Roots Data
WBS 99OP-11223.P.01.01.01 First Line Item
WBS 99OP-11223.P.01.01.02 Second Line Products
WBS 99OP-11223.P.01.01.03 Third Line Items

<colgroup><col><col></colgroup><tbody>
</tbody>

Second Table

Structure	Value
99OP-11223.P.01	25,254,759
99OP-11223.P.01.01	25,254,759
99OP-11223.P.01.01.01	1,910,442
99OP-11223.P.01.01.02	7,176,356
99OP-11223.P.01.01.03	3,796,289

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[steve the fish]

Hi. See if this works for you:

=INDEX($B$9:$B$13,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(" "&$A$9:$A$13&" "," "&A2&" ")),0),0))

Table 1 in A1:B6. Table 2 in A8:B13

 

[Weazel]

something like...

B3=LOOKUP(2^15,SEARCH($A$11:$A$15,A3),$B$11:$B$15)

Row\Col	A​	B​
1​	First Table
2​	Structure	Value​
3​	** WBS 99OP-11223.P.01 Project RT	25254759​
4​	* WBS 99OP-11223.P.01.01 Project Roots Data	25254759​
5​	WBS 99OP-11223.P.01.01.01 First Line Item	1910442​
6​	WBS 99OP-11223.P.01.01.02 Second Line Products	7176356​
7​	WBS 99OP-11223.P.01.01.03 Third Line Items	3796289​
8​
9​	Second Table
10​	Structure	Value​
11​	99OP-11223.P.01	25,254,759​
12​	99OP-11223.P.01.01	25,254,759​
13​	99OP-11223.P.01.01.01	1,910,442​
14​	99OP-11223.P.01.01.02	7,176,356​
15​	99OP-11223.P.01.01.03	3,796,289​

<tbody>
</tbody>

 

[Nishant94]

Unknown
	A	B
1	Structure	Value
2	** WBS 99OP-11223.P.01 Project RT	25254759
3	* WBS 99OP-11223.P.01.01 Project Roots Data	25254759
4	WBS 99OP-11223.P.01.01.01 First Line Item	1910442
5	WBS 99OP-11223.P.01.01.02 Second Line Products	7176356
6	WBS 99OP-11223.P.01.01.03 Third Line Items	3796289
7
8
9	Structure	Value
10	99OP-11223.P.01	2,52,54,759
11	99OP-11223.P.01.01	2,52,54,759
12	99OP-11223.P.01.01.01	19,10,442
13	99OP-11223.P.01.01.02	71,76,356
14	99OP-11223.P.01.01.03	37,96,289
15
12 bom page

Cell Formulas
Range	Formula
B2	=INDEX($B$10:$B$14,AGGREGATE(14,6,ISNUMBER(SEARCH($A$10:$A$14,A2))*(ROW($A$10:$A$14)-ROW($A$10)+1),1))

 

[raheem]

This worked great as desired with a little modification. Search should be SEARCH(" "&$A$11:$A$15&" "," "&A3&" "). Otherwise it will search also the value with shorter if exact string is not found.

Thanks for the tip.

 

[raheem]

something like...

B3=LOOKUP(2^15,SEARCH($A$11:$A$15,A3),$B$11:$B$15)

Row\Col	A​	B​
1​	First Table
2​	Structure	Value​
3​	** WBS 99OP-11223.P.01 Project RT	25254759​
4​	* WBS 99OP-11223.P.01.01 Project Roots Data	25254759​
5​	WBS 99OP-11223.P.01.01.01 First Line Item	1910442​
6​	WBS 99OP-11223.P.01.01.02 Second Line Products	7176356​
7​	WBS 99OP-11223.P.01.01.03 Third Line Items	3796289​
8​
9​	Second Table
10​	Structure	Value​
11​	99OP-11223.P.01	25,254,759​
12​	99OP-11223.P.01.01	25,254,759​
13​	99OP-11223.P.01.01.01	1,910,442​
14​	99OP-11223.P.01.01.02	7,176,356​
15​	99OP-11223.P.01.01.03	3,796,289​

<tbody>
</tbody>

This worked great as desired with a little modification. Search should be SEARCH(" "&$A$11:$A$15&" "," "&A3&" "). Otherwise it will search also the value with shorter if exact string is not found.

Thanks for the tip.​