Thanks Bernie & another question

G

Glen Sharpe

Your solution worked great! I'm now trying to come up with a
spreadsheet as follows.
Say I have a group of listings of parts we call assemblies in column
B. These groups would each have a different name such as group1 etc. Each
assembly would consist of 4 different parts in column C.
What I would like to do is enter "1" for assembly 1 or "2" for assembly
2 and have excel find the 4 different parts for this assembly and bring them
into cells d4 thru d8 along with their costs into column e. I've been trying
different functions for this but just can't seem to get it. Thanks!
 
B

Bernie Deitrick

Glen,

Assumptions: Your data table is in columns B, C, and D, with assembly name in B, part name in C, and
price in D, and the data starts in row 2 and continues down to row 1000. Row 1 has headers.

In F4:F7, enter the numbers 1,2,3, and 4.
In G3, enter the assembly name of interest. This must match exactly one of the values in column B.
In G4, array enter this formula (enter using Ctrl-Shift-Enter)

=INDEX(C:C,SMALL(IF(Sheet1!$B$2:$B$1000=$G$3,ROW(Sheet1!$B$2:$B$1000),10000),$F4),1)

Copy this formula, and paste in G4:H7.

HTH,
Bernie
MS Excel MVP
 
Top