Vlookup based on an IF function - Help Needed!

[Jimmy67]

Hi

This is tricky to explain so please bare with me but I have lots of data in the description column A below and I want to perform a search that says, if any of the cells in column A contain any of the medicines in column H, then return the relevant job code from column I.

So for example on the second line, there is water for injection in column A. I am hoping someone can give me a formula that will bring back job code "XRNEPPIM" from column I based on "Water" in column H. I hope that makes sense. Any help appreciated as have been trying different formulas and not getting anywhere. Thanks

 

[Eric W]

Try:

Book2
	A			D	E	F	G	H	I
1	Description			Job	Job description			Product	Job Code
2	M12 ACCRUE TPS STOGARE CHARGES			#N/A				PRALIDOXIME	XRNEPEPM
3	XXN12590103-SERVICE-3627608 - Water for Injection BP, 10ml Plasti			XRNEPPIM				PROPOFOL	XRNEPICU
4	AUTO GENERATED			#N/A				PROPOVEN	XRNEPICU
5	XXN11596699-SERVICE-LOT 1 - Storage Charges for RELENZA Qty 500			XRNEPPIM				PRPS	XRNEPEPM
6								PRUSSIIAN	XRNEPEPM
7								RELENZA	XRNEPPIM
8								REMIFENTANIL	XRNEPICU
9								ROCURONIUM	XRNEPICU
10								SOD CHLOR	XRNEPPIM
11								TAMIFLU	XRNEPPIM
12								WATER	XRNEPPIM
13
Sheet3

Cell Formulas
Range	Formula
D2:D5	D2	=LOOKUP(2,1/SEARCH($H$2:$H$12,A2),$I$2:$I$12)

 

[Jimmy67]

Yes that has worked perfectly! Thank you for the quick response, that is great