VLOOKUP between company names and stock tickers

[gilesgerman]

I have a number of company names in an excel spreadsheet like this.

Aceto CorpAZJ:BER

Petra Diamonds LtdPDL:LSE

OutokumpuOUT1V:HEX <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

I want to do a VLOOKUP to the following list

AZJ
PDL
OUT1V

Can someone provide me some code to do this?

Giles

 

[JumpingCrab]

With you list of 3 company names in A1:C1, and your lookup values in A5:A7, you can lookup the company name as follows:

B2=VLOOKUP("*"&A5&"*",$A$1:$A$3,1,0)

Hope this helps

 

[gilesgerman]

This helped a lot thanks

Also is there anyway to get excel to look at the company and extract the shortened version in the cell next to it?

 

[Aladin Akyurek]

It's not clear what the look up value, the match range, and result range are. Care to clarify?

 

[JumpingCrab]

This helped a lot thanks

Also is there anyway to get excel to look at the company and extract the shortened version in the cell next to it?

Is the ticker code included in a consistent way integrated in the company name string? The string after the ":" appears to be the stock exchange where the fund is listed.
Is the company name itself (without code and stock exchange) the search string?

 

[gilesgerman]

Is the ticker code included in a consistent way integrated in the company name string? The string after the ":" appears to be the stock exchange where the fund is listed.
Is the company name itself (without code and stock exchange) the search string?

The above is actually a bad example. The four below illustrate it much better so please can you look at those. The assumption should be that the information I want is before the ":" but after the final space " ". The exchange comes after the ":" and the stock can be and combination of 3 or 4 letters or numbers. I've tried to give a broad range of examples as follows: -

Aaron's Inc AAN:NYQ ------> AAN

Adamis Pharmaceuticals Corp CY3B:FRA -----> CY3B <colgroup><col></colgroup><tbody> </tbody>

Ab Dynamics PLC ABDP:LSE ------> ABDP

<colgroup><col></colgroup><tbody>
</tbody>

Adtec Plasma Technology Co Ltd 6668:TYO ------> 6668 Addus Homecare Corp A41:FRA ------> A41 <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[JumpingCrab]

If the code is always 3 or 4 characters and preceded by a space, this should work:

=TRIM(RIGHT(LEFT(B11,FIND(":",B11)-1),4))

Where B11 is the cell with the full company name string

 

[JumpingCrab]

If you have a maximum of 6 characters for the stock code, you can do this:

=RIGHT(RIGHT(LEFT(B7,FIND(":",B7)-1),6),LEN(RIGHT(LEFT(B7,FIND(":",B7)-1),6))-IFERROR(FIND(" ",RIGHT(LEFT(B7,FIND(":",B7)-1),6)),0))

It does assume that you have at least two characters before the last space that leads the ticker code. It gets a bit more interesting to debug though
The IFERROR is needed because FIND throws an error if it does not find a space.

Cheers,
JL

 

[Aladin Akyurek]

It seems you have a list of tickers. Let's name the range housing the list TickerList.

Now invoke the following formula to determine which ticker is imbedded in each string containing a colon.

=LOOKUP(9.99999999999999E+307,SEARCH(" "&TickerList&":",$A2),TickerList)

 

[JumpingCrab]

Aladin,

I think the OP wants to extract the ticker code from a text that has the company name, ticker code and exchange embedded in a single string. So he does not necessarily have the ticker code yet.

JL