Index with mulitple value returns and muliple column returns

[solar+CSE]

Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$67=Sheet1!$A$2,ROW(Sheet2!$A$1:$A$67)),ROW(1:1)),2)

Is there a way to return multiple column values from the same row?

1 2 3
A w x y
B q r s
A m n o

I want the return values to read across a row if looking for A:
w x y
m n o

Thanks for any help.

 

[Shane Devenshire]

Hi,

why not apply a AutoFilter on the column with A, B,... and filter for A.

 

[T. Valko]

Try it like this...

Array entered

=INDEX(Sheet2!B$1:B$67,SMALL(IF(Sheet2!$A$1:$A$67=Sheet1!$A$2,ROW(Sheet2!B$1:B$67)),ROWS(A$1:A1)))

Copy across then down.

That assumes your data really does start on row 1. If it doesn't then you'll
need to tweak the formula.

 

[solar+CSE]

Thank you, that worked after some tweaking to my actual spreadsheet.

 

[T. Valko]

You're welcome. Thanks for the feedback!