Search for text within cell, then return contents of that cell

**stuebss** · 2014-06-19, 04:20 PM

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!

**p45cal** · 2014-06-19, 06:17 PM

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