# Index and Match or vlookup?

#### ddub25

##### Well-known Member
Can anyone help with a formula for the below problem. I am not sure whether this can be done with a basic 'Index and Match or vlookup' formula or whether it needs to be done in visual basic. Anything I have tried doesn't seem to work.

Table 1 is a list of sub-assemblies and the materials and components required for each sub assembly. Table 2 simply lists all of the materials and components used across all the sub-assemblies and totals the quantity required.

Some pointers in the right direction would be much appreciated. Thanks

Dan

 Table 1 Table 2 Item Qty Item Qty M20 Nut 10​ M20 Nut 15​ M20 Thread 10​ M20 Thread 15​ 18mm Ply Sheet 4​ 18mm Ply Sheet 6​ 12mm Ply Sheet 4​ 12mm Ply Sheet 10​ 4 x 2 Timber 15​ 4 x 2 Timber 41​ 2 x 2 Timber 10​ 2 x 2 Timber 40​ 3 x 2 Timber 12​ 3 x 2 Timber 12​ M20 Nut 5​ M20 Thread 5​ 18mm Ply Sheet 2​ 4 x 2 Timber 6​ 18mm Ply Sheet 4​ 4 x 2 Timber 20​ 12mm Ply Sheet 6​ 2 x 2 Timber 30​

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### jasonb75

##### Well-known Member
Have you tried a pivot table?

Using Table 1 Items for row labels and Sum of Qty for Values would give you the same output as table 2.

#### ddub25

##### Well-known Member
Jason

Thanks very much, that very useful. I've not used pivot tables before. Thanks again

Dan

#### hnsd24_CN

##### Board Regular
You can also try the advanced filtering to extract non duplicate values. Then sum it with SUMIF.

#### ddub25

##### Well-known Member

I have been playing around with pivot tables a bit and realised that I would actually like to output my Pivot table with the results as below (I've added some extra columns). I can't seem to get the pivot table to display like this with the options within the pivot table dialogue.

I would also like the pivot to display the items in order as with the table I have suggested - 'M20 Nut' is the first item at the top of the table, 'M20 thread' is the 2nd item and so on as these items would have been found first at the top of table 1.

Can anyone help with getting the output to display like I have suggested? Thanks

Dan

 Table 1 Pivot Table Item Source Qty Total Price Item Source Qty Total M20 Nut Nuts & Bolts Ltd 10​ £ 5.00 M20 Nut Nuts & Bolts Ltd 15​ £ 7.50 M20 Thread Nuts & Bolts Ltd 10​ £ 5.00 M20 Thread Nuts & Bolts Ltd 15​ £ 7.50 18mm Ply Sheet Sheet Materials Ltd 4​ £ 80.00 18mm Ply Sheet Sheet Materials Ltd 10​ £ 200.00 4 x 2 Timber Timber Ltd 15​ £ 15.00 4 x 2 Timber Timber Ltd 41​ £ 41.00 2 x 2 Timber Timber Ltd 10​ £ 5.00 2 x 2 Timber Timber Ltd 40​ £ 20.00 12mm Ply Sheet Sheet Materials Ltd 4​ £ 60.00 12mm Ply Sheet Sheet Materials Ltd 10​ £ 150.00 3 x 2 Timber Timber Ltd 12​ £ 15.00 3 x 2 Timber Timber Ltd 12​ £ 15.00 M20 Nut Nuts & Bolts Ltd 5​ £ 2.50 M20 Thread Nuts & Bolts Ltd 5​ £ 2.50 18mm Ply Sheet Sheet Materials Ltd 2​ £ 40.00 4 x 2 Timber Timber Ltd 6​ £ 6.00 18mm Ply Sheet Sheet Materials Ltd 4​ £ 80.00 4 x 2 Timber Timber Ltd 20​ £ 20.00 12mm Ply Sheet Sheet Materials Ltd 6​ £ 90.00 2 x 2 Timber Timber Ltd 30​ £ 15.00

#### jasonb75

##### Well-known Member
This is the closest you will be able to get with a pivot table, you can only have text labels in the first column. The 2 index columns could be hidden to tidy things up.
Book1
ABCDEFGHIJ
1Table 1
2ItemSourceQtyTotal PriceIndexRow LabelsMin of IndexSum of QtySum of Total Price
3M20 NutNuts & Bolts Ltd10£5.001M20 Nut115£7.50
4M20 ThreadNuts & Bolts Ltd10£5.002Nuts & Bolts Ltd115£7.50
518mm Ply SheetSheet Materials Ltd4£80.003M20 Thread215£7.50
64 x 2 TimberTimber Ltd15£15.004Nuts & Bolts Ltd215£7.50
72 x 2 TimberTimber Ltd10£5.00518mm Ply Sheet310£200.00
812mm Ply SheetSheet Materials Ltd4£60.006Sheet Materials Ltd310£200.00
93 x 2 TimberTimber Ltd12£15.0074 x 2 Timber441£41.00
10M20 NutNuts & Bolts Ltd5£2.508Timber Ltd441£41.00
11M20 ThreadNuts & Bolts Ltd5£2.5092 x 2 Timber540£20.00
1218mm Ply SheetSheet Materials Ltd2£40.0010Timber Ltd540£20.00
134 x 2 TimberTimber Ltd6£6.001112mm Ply Sheet610£150.00
1418mm Ply SheetSheet Materials Ltd4£80.0012Sheet Materials Ltd610£150.00
154 x 2 TimberTimber Ltd20£20.00133 x 2 Timber712£15.00
1612mm Ply SheetSheet Materials Ltd6£90.0014Timber Ltd712£15.00
172 x 2 TimberTimber Ltd30£15.0015Grand Total1143£441.00
Sheet1
Cell Formulas
RangeFormula
E3:E17E3=COUNT(D\$3:D3)

If that is not close enough then I think that your exact layout should be possible with power query (I believe it should work but have not tried it yet). Other than that you would need some complex and inefficient formulas to do the task.

#### Fluff

##### MrExcel MVP, Moderator
@ddub25
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Replies
8
Views
39
Replies
3
Views
74
Replies
9
Views
219
Replies
11
Views
149
Replies
4
Views
128

1,129,874
Messages
5,638,781
Members
417,052
Latest member
Noobest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back