# return multiple columns with INDEX/MATCH

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!

Eric

P

#### Paul

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!

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

#### Alan Beban

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

Alan Beban

H

#### 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?

E

#### ebensen70

Thanks for the help!

Eri