formula to enter a % based on a word that appears in a cell

[dstepan]

Hi all

I can't even begin to determine how to approach what I would like to happen. Can I use a formula or should I use code?

I have an Inv # in col "A" and a Description in col "I".
I also have a table ("wordpercent") on a sheet "Info" with a list of words - Col "N" and a corresponding % - Col "O".

I want to place a formula in col "J" to return a corresponding % if any word in the Description col - Col "I" is listed in the table, and if it can't find a word in the list to then look at the Inv # - Col "A" and if it begins with a "1" or "6" then return the corresponding % - Col "O"

Below is a sampling of the data on "Sheet2". The actually data is hundreds of rows long.

Any help or ideas, even if it's a negative "no can do" is greatly appreciated.
Thank you

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Inv #	Customer	Order Qty	Unit Price	Ext Price	Status	Date	PO	Description	%	Profit			Oil	1%
2	15759-1	a	1	15.00	$15.00	Pending	####		Warranty Work		0			Warranty	4%
3	16420-1	b	1	28.00	$28.00	Complete	####		Warranty Leak Repair		0			inbound	4%
4	16426-1	b	1	22.00	$22.00	Complete	####		T&M Repair Work		0			outbound	4%
5	16521-1	b	1	29.00	$29.00	Complete	####		removal and shipment		0			parts	17%
6	16571-1	a	1	10.00	$10.00	Complete	####		CEDA Warranty Work		0			CEDA	2%
7	16616D-O	a	114	2.70	$307.80	Active	####		Type II Oil delivery		0			FWI	4%
8	16616E-O	a	113	2.70	$305.10	Active	####		Type II Oil Delivery		0			FWO	4%
9	16616F-O	a	112	2.70	$302.40	Active	####		Type II Oil Delivery		0			iInv # begins with 1	4%
10	16616G-O	a	111	2.70	$299.70	Active	####		Type II Oil Delivery		0			Inv # begins with 6	2%
11	16624A-2	b	1	19.00	$19.00	Complete	####		Assist in repair work		0
12	16662-1	e	1	26.00	$26.00	Pending	####				0
13	167	o	1	0.00	$0.00	Hold	####				0
14	16705	s	1	5.00	$5.00	Complete	####		Counter		0
15	16712-1	p	1	19.50	$19.50	Pending	####		Warranty Work		0
16	61551	x	1	36.00	$3,666.20	Complete	####		Various parts		0
17	61552	f	1	47.00	########	Active	####		10 MRE Repair{DC}		0
18	61552FWI	f	1	2.60	$2,690.00	Complete	####		2 MRE Inbound Frt{DC}		0
19	61552FWO	f	1	2.60	$2,690.00	Hold	####		2 MRE Outbound Frt{DC}		0

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

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

 

[Eric W]

Assuming your table on the Info sheet is in N2:O10, try this formula in J2 and copy down:

=IFERROR(LOOKUP(2^16,SEARCH(Info!$N$2:$N$10,I2)/(Info!$N$2:$N$10<>""),Info!$O$2:$O$10),IF(OR(LEFT(A2)={"1","6"}),O2,"no match"))