Index-Match, with Like or some other Function

[ryguy7272]

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''.

 

[Jacob Skaria]

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$1000,SMALL(IF(LEFT($D$1:$D$1000,FIND(".",$F$1))=
LEFT($F$1,FIND(".",$F$1)),ROW($D$1:$D$1000)),ROW(A1))))

If this post helps click Yes

 

[ryguy7272]

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--

 

[Jacob Skaria]

Maybe I have misunderstood your question. Do you mean a lookup ?

=INDEX(E:E,MATCH(LEFT(A1,FIND(".",A1)) & "*",D,0))

If this post helps click Yes

 

[ryguy7272]

That function finds the first match, but not subsequent matches. Seems to
work like a vlookup.

What I'd like to do is look for a value in A2, make sure it is in Col D, and
then find the corresponding value in B2. Then look in A3, make sure it is in
Col D, and then find the corresponding value in B3.

Does it make sense?

TIA!!
Ryan--

 

[Jacob Skaria]

Ryan; why dont you post few examples .

=INDEX(E:E,MATCH(A1,D,0))

If this post helps click Yes

 

[Jacob Skaria]

Ryan, I read your post in the other group..bit confusing..I think my original
solution will help..which picks up all matching entries from ColD and ColE.
if you are unable to make it work send me the file.

 

[ryguy7272]

I got it! I got it! This was the solution:
=INDEX(E:E,MATCH(LEFT(A1,FIND(".",A1)) & "*",D,0))

Not sure what happened before; I must have screwed up a reference somehow
when I made a small change to customize it a bit.

Thanks for everything Jacob! Very helpful!! This will definitely be saved
in my library of useful functions!!!

Ryan---