How to look up and return multiple values
 

I am trying to look up a value in one column of a table and return data from
the corresponding row in the next column over. VLOOKUP only returns the
first instance of the lookup value, and I want to show each instance.

For example: my table lists dates in one column and tasks in the next and 4
tasks are scheduled on the same date, VLOOKUP only returns the name of the
task coresponding to the first cell it found the date in. I want it to
return all 4 task names.

Is there another function that will do this?

Thanks

Hi
try the following kind of array formula (entered with CTRL+SHIFT+ENTER)
=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100="lookup
value",ROW($A$1:$A$100)),ROW(1:1)))
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany


Wendy wrote:
I am trying to look up a value in one column of a table and return
data from the corresponding row in the next column over. VLOOKUP
only returns the first instance of the lookup value, and I want to
show each instance.

For example: my table lists dates in one column and tasks in the next
and 4 tasks are scheduled on the same date, VLOOKUP only returns the
name of the task coresponding to the first cell it found the date in.
I want it to return all 4 task names.

Is there another function that will do this?

Thanks

Wendy wrote:

I am trying to look up a value in one column of a table and return data from
the corresponding row in the next column over. VLOOKUP only returns the
first instance of the lookup value, and I want to show each instance.

For example: my table lists dates in one column and tasks in the next and 4
tasks are scheduled on the same date, VLOOKUP only returns the name of the
task coresponding to the first cell it found the date in. I want it to
return all 4 task names.

Is there another function that will do this?

Thanks
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
VLookups function will return your desired result.

Alan Beban

Thanks everyone!

"Wendy" wrote:

I am trying to look up a value in one column of a table and return data from
the corresponding row in the next column over. VLOOKUP only returns the
first instance of the lookup value, and I want to show each instance.

For example: my table lists dates in one column and tasks in the next and 4
tasks are scheduled on the same date, VLOOKUP only returns the name of the
task coresponding to the first cell it found the date in. I want it to
return all 4 task names.

Is there another function that will do this?

Thanks