Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Wendy
 
Posts: n/a
Default 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
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
Alan Beban
 
Posts: n/a
Default

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
  #4   Report Post  
Wendy
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I lookup and return different values when the lookup value. kg Excel Discussion (Misc queries) 1 January 20th 05 01:53 AM
Generating Correlated Random Values in Excel Randy Excel Discussion (Misc queries) 2 January 16th 05 10:50 PM
Pivot Table Zero Value jcliquidtension Excel Discussion (Misc queries) 12 January 13th 05 05:21 AM
How do you calculate the difference between two values within a p. emlouise Excel Discussion (Misc queries) 2 December 10th 04 04:13 AM
Can vlookup return multiple matches in a single cell? cchristensen Excel Discussion (Misc queries) 3 December 2nd 04 02:56 AM


All times are GMT +1. The time now is 09:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"