Lookup to return multiple rows

[rhudgins]

In the example is there a lookup funtion that can identify ALK in B3 and then find ALK in F3:S3 and then copy G4:G23 into cell B4?

[darkyam]

To be clear, do you want all the values returned in separate cells under B3 (assumed) or concatenated or returned as an array?

[rhudgins]

Yes, I would like the vales returned in seperate cells under B3

[darkyam]

Ok, try =INDEX(F:S,ROW(),MATCH($B$3,$F$3:$S$3,0)) in B4 copied down, then format to 0;; to hide 0s. Alternatively, you can wrap it in an IF statement, such as =IF(INDEX(F:S,ROW(),MATCH($B$3,$F$3:$S$3,0))="","",INDEX(F:S,ROW(),MATCH($B$3,$F$3:$S$3,0)))

[rhudgins]

That works but my master data sheet is set up so instead of starting in B4 I need to start it in a random cell such as B30. Can you re-right the statement to make this work?

[darkyam]

Are the other ranges in the same place?

If so, use =INDEX(F:S,ROWS($31:31)+3,MATCH($B$3,$F$3:$S$3,0)), where 31 is the first row you use the formula in and 3 is the number of rows (including header) before the looked up values begin.

[rhudgins]

This is my exacy spread sheet. I would like to paste the data in cell F67 then F88 then F109 ect..

[darkyam]

=INDEX($AA$6:$AN$30,(ROW()-66)/21+1,MATCH($F$66,$AA$6:$AN$6,0))

While I appreciate the effort to show an example right off the bat, I hope that this exchange underscores the importance of making sure your example properly reflects your actual data layout.

[rhudgins]

Sorry for the confusion. This is the formula I will use. How do i format the 0's to be blank cells?

=INDEX(AA:AN,ROWS($67:67)+6,MATCH($F$66,$AA$6:$AN$6,0))

[darkyam]

That's not going to work because you don't take the fact that it's only every 21 cells into account.

Format the cells to General;; to take care of 0s.

[rhudgins]

Essentially I need the most efficient way to copy and paste the appropriate group of tickers below each ticker in column F and I think this is the best way to do it.