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
[TABLE="class: grid, width: 500"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Structure[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD]** WBS 99OP-11223.P.01 Project RT[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]* WBS 99OP-11223.P.01.01 Project Roots Data[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] WBS 99OP-11223.P.01.01.01 First Line Item[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] WBS 99OP-11223.P.01.01.02 Second Line Products[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] WBS 99OP-11223.P.01.01.03 Third Line Items[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Second Table
[TABLE="class: grid, width: 476"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Structure[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD]99OP-11223.P.01[/TD]
[TD="align: right"]25,254,759[/TD]
[/TR]
[TR]
[TD]99OP-11223.P.01.01[/TD]
[TD="align: right"]25,254,759[/TD]
[/TR]
[TR]
[TD]99OP-11223.P.01.01.01[/TD]
[TD="align: right"]1,910,442[/TD]
[/TR]
[TR]
[TD]99OP-11223.P.01.01.02[/TD]
[TD="align: right"]7,176,356[/TD]
[/TR]
[TR]
[TD]99OP-11223.P.01.01.03[/TD]
[TD="align: right"]3,796,289[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

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

1​

[/TD]
[TD]First Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

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

Value​

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

3​

[/TD]
[TD]** WBS 99OP-11223.P.01 Project RT[/TD]
[TD]

25254759​

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

4​

[/TD]
[TD]* WBS 99OP-11223.P.01.01 Project Roots Data[/TD]
[TD]

25254759​

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

5​

[/TD]
[TD]WBS 99OP-11223.P.01.01.01 First Line Item[/TD]
[TD]

1910442​

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

6​

[/TD]
[TD]WBS 99OP-11223.P.01.01.02 Second Line Products[/TD]
[TD]

7176356​

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

7​

[/TD]
[TD]WBS 99OP-11223.P.01.01.03 Third Line Items[/TD]
[TD]

3796289​

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

8​

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

9​

[/TD]
[TD]Second Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

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

Value​

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

11​

[/TD]
[TD]99OP-11223.P.01[/TD]
[TD]

25,254,759​

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

12​

[/TD]
[TD]99OP-11223.P.01.01[/TD]
[TD]

25,254,759​

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

13​

[/TD]
[TD]99OP-11223.P.01.01.01[/TD]
[TD]

1,910,442​

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

14​

[/TD]
[TD]99OP-11223.P.01.01.02[/TD]
[TD]

7,176,356​

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

15​

[/TD]
[TD]99OP-11223.P.01.01.03[/TD]
[TD]

3,796,289​

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

 

[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)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

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

1​

[/TD]
[TD]First Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

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

Value​

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

3​

[/TD]
[TD]** WBS 99OP-11223.P.01 Project RT[/TD]
[TD]

25254759​

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

4​

[/TD]
[TD]* WBS 99OP-11223.P.01.01 Project Roots Data[/TD]
[TD]

25254759​

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

5​

[/TD]
[TD]WBS 99OP-11223.P.01.01.01 First Line Item[/TD]
[TD]

1910442​

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

6​

[/TD]
[TD]WBS 99OP-11223.P.01.01.02 Second Line Products[/TD]
[TD]

7176356​

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

7​

[/TD]
[TD]WBS 99OP-11223.P.01.01.03 Third Line Items[/TD]
[TD]

3796289​

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

8​

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

9​

[/TD]
[TD]Second Table[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

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

Value​

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

11​

[/TD]
[TD]99OP-11223.P.01[/TD]
[TD]

25,254,759​

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

12​

[/TD]
[TD]99OP-11223.P.01.01[/TD]
[TD]

25,254,759​

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

13​

[/TD]
[TD]99OP-11223.P.01.01.01[/TD]
[TD]

1,910,442​

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

14​

[/TD]
[TD]99OP-11223.P.01.01.02[/TD]
[TD]

7,176,356​

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

15​

[/TD]
[TD]99OP-11223.P.01.01.03[/TD]
[TD]

3,796,289​

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

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.​