| NCICVMAKE.xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 3 | Description | MakeCode | Policy | ResultIWant | ||
| 4 | ACURA | ACUR | TOYOTAPICKUP | TOYT | ||
| 5 | HUMMER | AMGN | GMCYUKONXL | GMC | ||
| 6 | AUTOCAR/WHITEMOTORCOMPANY | AUTO | MACKCH613 | MACK | ||
| 7 | AUTOCAR | AUTO | MACKTRACTOR | MACK | ||
| 8 | BMW | BMW | FORD1TONWINCHTRUCK | FORD | ||
| 9 | BUICK | BUIC | FORD1TONPU | FORD | ||
| 10 | CADILLAC | CADI | FORDF-350 | FORD | ||
| 11 | CHEVROLET | CHEV | FORDF350PU | FORD | ||
| 12 | CHEV | CHEV | FORDF350PU | FORD | ||
| 13 | CHEVY | CHEV | FORDF-350 | FORD | ||
| 14 | CHRYSLER | CHRY | FORDF-350 | FORD | ||
| 15 | COOPER | COOP | FORD1TONWINCHTRUCK | FORD | ||
| 16 | DAEWOO | DAEW | FORDF350TRUCK&WINCH | FORD | ||
| 17 | DODGE | DODG | FORDF350TRUCKWINCH | FORD | ||
| 18 | FORD | FORD | FORDF-350 | FORD | ||
| 19 | FREIGHTLINER | FRHT | FORD6X4``LTSERIES106.4BBC | FORD | ||
| 20 | FREIGHTLINERCORP. | FRHT | INT'LWINCHTRUCK | INTL | ||
| 21 | GEO | GEO | FORDCROWNVICTORIA | FORD | ||
| 22 | GENERALMOTORSCORPORATION | GM | INTERNATIONALBUCKETTRUCK | INTL | ||
| 23 | GMC | GMC | ||||
| 24 | GENERALMOTORSCORPORATION | GMC | ||||
| 25 | HONDA | HOND | ||||
| 26 | HYUNDAI | HYUN | ||||
| 27 | INFINITI | INFI | ||||
| 28 | INT'L | INTL | ||||
| 29 | INTL | INTL | ||||
| 30 | INTERNATIONAL | INTL | ||||
Common | ||||||
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
FORMULA QUESTION
- Thread starter acc070
- Start date
Oaktree
MrExcel MVP
- Joined
- Jun 20, 2002
- Messages
- 8,120
- Office Version
- 365
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.
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.
Upvote
0
NBVC
Well-known Member
- Joined
- Aug 31, 2005
- Messages
- 5,828
Here's one solution:
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.
| Book1Sep12 (version 1).xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 3 | Description | Make Code | Policy | Result I Want | ||
| 4 | ACURA | ACUR | TOYOTA PICKUP | TOYT | ||
| 5 | HUMMER | AMGN | GMC YUKON XL | GMC | ||
| 6 | AUTOCAR/WHITE MOTOR COMPANY | AUTO | MACK CH613 | MACK | ||
| 7 | AUTOCAR | AUTO | MACK TRACTOR | MACK | ||
| 8 | BMW | BMW | FORD 1 TON WINCH TRUCK | FORD | ||
| 9 | BUICK | BUIC | FORD 1 TON PU | FORD | ||
| 10 | CADILLAC | CADI | FORD F-350 | FORD | ||
| 11 | CHEVROLET | CHEV | FORD F350 PU | FORD | ||
| 12 | CHEV | CHEV | FORD F350 PU | FORD | ||
| 13 | CHEVY | CHEV | FORD F-350 | FORD | ||
| 14 | CHRYSLER | CHRY | FORD F-350 | FORD | ||
| 15 | COOPER | COOP | FORD 1 TON WINCH TRUCK | FORD | ||
| 16 | DAEWOO | DAEW | FORD F350 TRUCK & WINCH | FORD | ||
| 17 | DODGE | DODG | FORD F350 TRUCK WINCH | FORD | ||
| 18 | FORD | FORD | FORD F-350 | FORD | ||
| 19 | FREIGHTLINER | FRHT | FORD 6X4 ``LT SERIES 106.4 BBC | FORD | ||
| 20 | FREIGHTLINER CORP. | FRHT | INT'L WINCH TRUCK | INTL | ||
| 21 | GEO | GEO | FORD CROWN VICTORIA | FORD | ||
| 22 | GENERAL MOTORS CORPORATION | GM | INTERNATIONAL BUCKET TRUCK | INTL | ||
| 23 | GMC | GMC | ||||
| 24 | GENERAL MOTORS CORPORATION | GMC | ||||
| 25 | HONDA | HOND | ||||
| 26 | HYUNDAI | HYUN | ||||
| 27 | INFINITI | INFI | ||||
| 28 | INT'L | INTL | ||||
| 29 | INTL | INTL | ||||
| 30 | INTERNATIONAL | INTL | ||||
| 31 | MACK | MACK | ||||
| 32 | TOYOTA | TOYT | ||||
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.
Upvote
0
