Insert a new column between columns A and B, and in the new B1:
=LEFT(A1,SEARCH(" ",A1)-1)
Copy down.
Then in E1:
=VLOOKUP(LEFT(D1,SEARCH(" ",D1)-1),$B$1:$C$7,2,0)
Copy down
So right now I have a spreadsheet that looks something like this:
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!
Insert a new column between columns A and B, and in the new B1:
=LEFT(A1,SEARCH(" ",A1)-1)
Copy down.
Then in E1:
=VLOOKUP(LEFT(D1,SEARCH(" ",D1)-1),$B$1:$C$7,2,0)
Copy down
Great, excellent. Thanks!
Bookmarks