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

D

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
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


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

[TABLE="width: 959"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Pass one step of code to the next
2
Replies
16
Views
978
HaHoBe
HaHoBe
M
  • Question Question
collecting information from other sheets only when fields have been completed?
Replies
1
Views
529
molten
M
J
  • Question Question
Minimum and maximum between columns and different places.
Replies
4
Views
843
Joeabr
J
A
  • Question Question
Conditional sum help?
Replies
5
Views
5K
stanleydgromjr
S

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top