how to lookup a value and return multiple corresponding values
 

how do i look up a value from a list and return multiple corresponding
values? In addtion, i want to have the corresponding values for the
subsequent colums.

Thanks.

how to lookup a value and return multiple corresponding values
 

Here is a formula that should do it. Put this in the first of the target
cells and copy down

=IF(ISERROR(SMALL(IF($A$1:$A$20="Bob",ROW($A$1:$A$ 20),""),ROW(A1))),"",INDEX
(B$1:$B$20,SMALL(IF($A$1:$A$20="Bob",ROW($A$1:$A$2 0),""),ROW(A1))))

it is an array formula, so commit with Ctrl-Shift-Enter.

Change column B for the other columns

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Asthee" wrote in message
...
how do i look up a value from a list and return multiple corresponding
values? In addtion, i want to have the corresponding values for the
subsequent colums.

Thanks.