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 !

Data	Formula Result
Bananboat Cleaner 435353	2000
Banaboat Cleaner 546456546	2000			Lookup String	Result
Cleaner 475464	2000			Cleaner	2000
Sydney Electricity 465464	3000			Electricity	3000
Melbourne Electricity 46546464	3000			Fiacon	4000
Electricity fjfh6765	3000			Rent	5000
576567 Electricity	3000			Fuji	6000
Electricity	3000			Macquarie	7000
Fiacon	4000
Flexirent	5000
Fuji Xerox fggdfgd55	6000
hgfhf Fuji Xerox	6000
Macquarie 44498	7000
999 Macquarie 987456	7000

<tbody>
</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