Returning looking up all values in a list

T

Ted Metro

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.
 
T

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top