return multiple columns with INDEX/MATCH

[ebensen70]

Hi,
I have an array of data that that I am picking values from using the
INDEX and MATCH functions. Is there a way to return more than one
column of data?

example:

10 A B C D
20 E F G H
30 K L M N
40 O P Q R

I want to search for values in column 1 that match a given value and
return the values in columns 2 through 4 using one formula.

Right now I would just do it one column at a time with:

=INDEX($A$1:$D$5,MATCH($X1,$A$1:$D$1,0),2)

and in the next cell to the left:

=INDEX($A$1:$D$5,MATCH($X1,$A$1:$D$1,0),3) and on...and on...

I'm sorry if I have not been clear.
Any help would be greatly appreciated!

Thanks in advance,

Eric

 

[Paul]

Hi,
I have an array of data that that I am picking values from using the
INDEX and MATCH functions. Is there a way to return more than one
column of data?

example:

10 A B C D
20 E F G H
30 K L M N
40 O P Q R

I want to search for values in column 1 that match a given value and
return the values in columns 2 through 4 using one formula.

Right now I would just do it one column at a time with:

=INDEX($A$1:$D$5,MATCH($X1,$A$1:$D$1,0),2)

and in the next cell to the left:

=INDEX($A$1:$D$5,MATCH($X1,$A$1:$D$1,0),3) and on...and on...

I'm sorry if I have not been clear.
Any help would be greatly appreciated!

Thanks in advance,

Eric

Why INDEX and MATCH rather than just VLOOKUP?
=VLOOKUP($X1,$A$1:$D$5,2,0)

A formula can only return one result. If you want to SUM the three
individual results to make one result, this can be done in one array
formula:
=SUM(VLOOKUP($X1,$A$1:$D$5,ROW($2:$4),0))
To enter an array formula, use Ctrl+Shift+Enter rather than just Enter.

 

[Alan Beban]

=VLOOKUP(X1,A15,{2,3,4},FALSE), array entered into a three-column row.

Alan Beban

 

[Harlan Grove]

...
A formula can only return one result. . . .

Pity. Used Excel all this time and never figured out what array formulas do.

. . . If you want to SUM the three
individual results to make one result, this can be done in one array
formula:
=SUM(VLOOKUP($X1,$A$1:$D$5,ROW($2:$4),0))
To enter an array formula, use Ctrl+Shift+Enter rather than just Enter.

So you have heard of array formula! Do you realize they can be entered into
multiple cell ranges?

 

[ebensen70]

Thanks for the help!

Eri