Search for text in string then lookup text in a separate table and show result

[dino2176]

Hello everyone,

First time poster, using excel 2007 installed Mrexcel HTML maker, didnt appear in excel, forum tools addin was pasting extra data per the test forums. I am sorry in advance for doing this the hard way.

I have data in the DATA column, and I want to search whats in the LOOKUP STRING table and give the corresponding result from the table. Thankyou all in advance !

Bananboat Cleaner 435353
Banaboat Cleaner 546456546			Lookup String	Result
Cleaner 475464
Sydney Electricity 465464
Melbourne Electricity 46546464
Electricity fjfh6765
576567 Electricity
Electricity
Fiacon
Flexirent
Fuji Xerox fggdfgd55
hgfhf Fuji Xerox
Macquarie 44498
999 Macquarie 987456

<tbody>
[TD="class: xl64"]Data[/TD]
[TD="class: xl64, width: 128, colspan: 2"]Formula Result[/TD]
[TD="width: 64"][/TD]
[TD="width: 92"][/TD]
[TD="width: 64"][/TD]

[TD="align: right"]2000[/TD]

[TD="align: right"]2000[/TD]

[TD="align: right"]2000[/TD]

[TD="class: xl67"]Cleaner[/TD]
[TD="class: xl65, align: right"]2000[/TD]

[TD="align: right"]3000[/TD]

[TD="class: xl68"]Electricity[/TD]
[TD="class: xl66, align: right"]3000[/TD]

[TD="align: right"]3000[/TD]

[TD="class: xl68"]Fiacon[/TD]
[TD="class: xl66, align: right"]4000[/TD]

[TD="align: right"]3000[/TD]

[TD="class: xl68"]Rent[/TD]
[TD="class: xl66, align: right"]5000[/TD]

[TD="align: right"]3000[/TD]

[TD="class: xl68"]Fuji[/TD]
[TD="class: xl66, align: right"]6000[/TD]

[TD="align: right"]3000[/TD]

[TD="class: xl68"]Macquarie[/TD]
[TD="class: xl66, align: right"]7000[/TD]

[TD="align: right"]4000[/TD]

[TD="align: right"]5000[/TD]

[TD="align: right"]6000[/TD]

[TD="align: right"]6000[/TD]

[TD="align: right"]7000[/TD]

[TD="align: right"]7000[/TD]

</tbody>

 

[Nishant94]

Unknown
	A	B	C	D	E
1	Data	Formula Result
2	Bananboat Cleaner 435353	2000
3	Banaboat Cleaner 546456546	2000		Lookup String	Result
4	Cleaner 475464	2000		Cleaner	2000
5	Sydney Electricity 465464	3000		Electricity	3000
6	Melbourne Electricity 46546464	3000		Fiacon	4000
7	Electricity fjfh6765	3000		Rent	5000
8	576567 Electricity	3000		Fuji	6000
9	Electricity	3000		Macquarie	7000
10	Fiacon	4000
11	Flexirent	5000
12	Fuji Xerox fggdfgd55	6000
13	hgfhf Fuji Xerox	6000
14	Macquarie 44498	7000
15	999 Macquarie 987456	7000
16
17
Sheet9

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(ISNUMBER(SEARCH($D$4:$D$9,A2))*$E$4:$E$9)

 

[dino2176]

I spent all day trying to figure it out, thank you very much @Nishant94