So right now I have a spreadsheet that looks something like this:
As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.
Right now I have:
=IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")
This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.
Anyone have insight on how to fix? Thanks!
| A | B | C | D | |
| APPLE INC | XOM | EXXON MOBILE CORP. | = | |
| BERKSHIRE HATH-A | BRK/A | BERKSHIRE HATHAWAY | ||
| WELLS FARGO & CO | WFC | WELLS FARGO & CO. | ||
| GENERAL ELECTRIC | GE | GENERAL ELECTRIC CO. | ||
| EXXON MOBIL CORP | CVX | APPLE INC. | ||
| WAL-MART STORES | WMT | WAL-MART STORES CO. | ||
| JPMORGAN CHASE | JPM | JPMORGAN CHASE & CO. |
As you can see, the stock names are slightly different in columns A & C (CORP. vs CORP, CO vs CO., etc). I need a formula in column D that searches column A for the first word in column C, and then retrieves the contents of that cell. I want to do this because I will then use a vlookup in column E to get the ticker for the stock.
Right now I have:
=IF(ISERROR(SEARCH(LEFT(C1,FIND(" ",C1)),A:A,1)),A:A,"")
This formula searches column A for EXXON, but does not return the contents of the cell. Instead, it returns the contents of a different cell in the column.
Anyone have insight on how to fix? Thanks!
