Returning looking up all values in a list
 

I have a list of project numbers and their costs, and some project numbers
are represented multiple times.

I want a lookup that will return all of the values for a project.

Column A Column B
154 100
143 150
153 75
143 92
176 68
122 84
143 111
110 225
176 374


I have a list of the unique project numbers in Column C

143
153
154
176
122
110

In column D I'd like to see the first cost, in Column E (where applicable)
the second cost, in Column F the third cost, etc.

When I do a vlookup I can't tell it to grab the second instance of the
project number, but there has to be a way.

Returning looking up all values in a list
 

Try this:

Entered as an array in D2 using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):

=IF(COLUMNS($A:A)<=COUNTIF($A$2:$A$10,$C2),INDEX($ B$2:$B$10,SMALL(IF($A$2:$A$10=$C2,ROW(B$2:B$10)-MIN(ROW(B$2:B$10))+1),COLUMNS($A:A))),"")

Copy down then across until you get a solid column of blanks.

Biff

"Ted Metro" wrote in message
...
I have a list of project numbers and their costs, and some project numbers
are represented multiple times.

I want a lookup that will return all of the values for a project.

Column A Column B
154 100
143 150
153 75
143 92
176 68
122 84
143 111
110 225
176 374


I have a list of the unique project numbers in Column C

143
153
154
176
122
110

In column D I'd like to see the first cost, in Column E (where applicable)
the second cost, in Column F the third cost, etc.

When I do a vlookup I can't tell it to grab the second instance of the
project number, but there has to be a way.