Thanks Jacob! Unfortunately, that gives me all blanks, everywhere. My data
really starts on Row 2, not row 1, but i think that is irrelevant since this
is array-entered, right.

Let me throw out another example. I have TGT.5000 Nov call in A2, TGT.5250
Dec call in A3, and TGT.5250 Nov call in A4. I have TGT.N in D2 (but it
could be anywhere in Col D, approx 200 rows). I want to look at the value in
D2, and pull in the corresponding stock prices in B2, B3, and B4. I've done
something similar before, but i couldn't find that function in my library,
and I'm not even sure that would work even if I could find it.

What else can I try?


Thanks!
Ryan--



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Jacob Skaria" wrote:

Hi Ryan

If I understand your correctly you want to lookup all stock symbols and
prices for a single query..say TGH.n should list down all TGH.* and its
corresponding prices

In Cell F1 type the symbol to be searched
In F2 enter the below formula (array entered)...Copy the formula down as
required.Also copy the same formula to ColG2 and copy down as required.

(array entered)
=IF(COUNTIF($D$1:$D$1000,LEFT($F$1,FIND(".",$F$1)) & "*")<ROW(A1),"",
INDEX(D$1:D$1000,SMALL(IF(LEFT($D$1:$D$1000,FIND(" .",$F$1))=
LEFT($F$1,FIND(".",$F$1)),ROW($D$1:$D$1000)),ROW(A 1))))

If this post helps click Yes
---------------
Jacob Skaria


"ryguy7272" wrote:

I have two lists, one with Stock Symbol and Sector (Column A and B), the
other with Stock Symbol and Price (Column D & E). I'm trying to figure out
how to lookup a Symbol in Column A, where there could be TGH.N and find all
matches in Column D, where the Symbols could be TGH.N, as well as TGH.400,
TGH.500, and TGH.600. The logic is, the first part of the symbol, up to the
period, will be the same. For instance, EL.400 and EL.500 are in the same
sector and EL.N, EL.400, and EL.500 are all in the same sector. I want to
lookup EL.N, EL.400, and EL.500 and find stock prices for each in Column E.

Thanks!
Ryan---



Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.