# FORMULA QUESTION

#### acc070

##### New Member
NCICVMAKE.xls
ABCD
3DescriptionMakeCodePolicyResultIWant
4ACURAACURTOYOTAPICKUPTOYT
5HUMMERAMGNGMCYUKONXLGMC
6AUTOCAR/WHITEMOTORCOMPANYAUTOMACKCH613MACK
7AUTOCARAUTOMACKTRACTORMACK
8BMWBMWFORD1TONWINCHTRUCKFORD
9BUICKBUICFORD1TONPUFORD
11CHEVROLETCHEVFORDF350PUFORD
12CHEVCHEVFORDF350PUFORD
13CHEVYCHEVFORDF-350FORD
14CHRYSLERCHRYFORDF-350FORD
15COOPERCOOPFORD1TONWINCHTRUCKFORD
16DAEWOODAEWFORDF350TRUCK&WINCHFORD
17DODGEDODGFORDF350TRUCKWINCHFORD
18FORDFORDFORDF-350FORD
19FREIGHTLINERFRHTFORD6X4``LTSERIES106.4BBCFORD
20FREIGHTLINERCORP.FRHTINT'LWINCHTRUCKINTL
21GEOGEOFORDCROWNVICTORIAFORD
22GENERALMOTORSCORPORATIONGMINTERNATIONALBUCKETTRUCKINTL
23GMCGMC
24GENERALMOTORSCORPORATIONGMC
25HONDAHOND
26HYUNDAIHYUN
27INFINITIINFI
28INT'LINTL
29INTLINTL
30INTERNATIONALINTL
Common

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Welcome to the board.

It is difficult to tell where your column breaks are. Perhaps you can repost using the HTML Maker utility (see the link at the bottom of the page) or, at the very least, using || or // or .... or something to show the breaks more clearly.

Further, can you give an example of what you're trying to return? What do you mean by "if the policy column contains any of the options in the description col to enter what is in col b"? What are the options in the description col? etc.

i RESUBMITTED WITH THE WORKSHEET.

MY SOURCE DATA IS COL C.
IF COL C CONTAINS COL A THEN PUT THE 4 LETTER CODE FROM COL B INTO COL D

IF COL C DOES NOT CONTAIN COL A THEN LEAVE COL D BLANK

Here's one solution:
Book1Sep12 (version 1).xls
ABCD
3DescriptionMake CodePolicyResult I Want
4ACURAACURTOYOTA PICKUPTOYT
5HUMMERAMGNGMC YUKON XLGMC
6AUTOCAR/WHITE MOTOR COMPANYAUTOMACK CH613MACK
7AUTOCARAUTOMACK TRACTORMACK
8BMWBMWFORD 1 TON WINCH TRUCKFORD
9BUICKBUICFORD 1 TON PUFORD
11CHEVROLETCHEVFORD F350 PUFORD
12CHEVCHEVFORD F350 PUFORD
13CHEVYCHEVFORD F-350FORD
14CHRYSLERCHRYFORD F-350FORD
15COOPERCOOPFORD 1 TON WINCH TRUCKFORD
16DAEWOODAEWFORD F350 TRUCK & WINCHFORD
17DODGEDODGFORD F350 TRUCK WINCHFORD
18FORDFORDFORD F-350FORD
19FREIGHTLINERFRHTFORD 6X4 ``LT SERIES 106.4 BBCFORD
20FREIGHTLINER CORP.FRHTINT'L WINCH TRUCKINTL
21GEOGEOFORD CROWN VICTORIAFORD
22GENERAL MOTORS CORPORATIONGMINTERNATIONAL BUCKET TRUCKINTL
23GMCGMC
24GENERAL MOTORS CORPORATIONGMC
25HONDAHOND
26HYUNDAIHYUN
27INFINITIINFI
28INT'LINTL
29INTLINTL
30INTERNATIONALINTL
31MACKMACK
32TOYOTATOYT
Sheet4

Formula in D3: =IF(OR(ISNUMBER(SEARCH(\$A\$4:\$A\$32,C4))),INDEX(\$B\$4:\$B\$32,SUM(IF(ISNUMBER(SEARCH(\$A\$4:\$A\$32,C4)),ROW(\$A\$4:\$A\$32)))-ROW(\$A\$3)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER...you should see {} brackets appear.

Change ranges to suit your data and then re-confirm with the CSE key combo.

Copy down the formula.

EDIT: Revised formula.

Replies
4
Views
143
Replies
3
Views
94
Replies
6
Views
213
Replies
3
Views
262
Replies
2
Views
73

1,196,487
Messages
6,015,489
Members
441,898
Latest member
kofafa

### 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.

### Which adblocker are you using?

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

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