more than vlookup

[Ali]

I want to be able to type in a word, such as REITS, and
pull in Ticker4 and Ticker5 in a column. How do I do
this?


Ticker1 Power
Ticker2 Power
Ticker3 Power
Ticker4 REITS
Ticker5 REITS

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=$C$1,ROW($B$1:$B$100)),ROW(1:1)
))
and copy down

where C1 stores your lookup value