return multiple columns with INDEX/MATCH

  • Thread starter Thread starter ebensen70
  • Start date Start date
E

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
 
ebensen70 said:
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.
 
...
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?
 
Back
Top