Index with mulitple value returns and muliple column returns
 

Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=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.

Index with mulitple value returns and muliple column returns
 

Hi,

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"solar+CSE" wrote:

Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=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.

Index with mulitple value returns and muliple column returns
 

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.

--
Biff
Microsoft Excel MVP


"solar+CSE" wrote in message
...
Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=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.

Index with mulitple value returns and muliple column returns
 

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"solar+CSE" wrote in message
...
Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=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.

Index with mulitple value returns and muliple column returns
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"solar+CSE" wrote in message
...
Thank you, that worked after some tweaking to my actual spreadsheet.

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"solar+CSE" wrote in message
...
Using
=INDEX(Sheet2!$A$1:$B$67,SMALL(IF(Sheet2!$A$1:$A$6 7=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.