Excel Foruma Help!!

[nuiisance]

I basically created two excel sheets. One with parts + price and another with a master sheet for components and price, basically on the parts sheet there are about 3000 line items of parts containing two components each.

I need a foruma that will grab the price data from the component master sheet and match it to the chosen components onto the parts sheet and then mark it up 50%.

There are about 20 components and each part uses one of the 20 components some the same some are different, I need a formula thatll match the components listed on the parts sheet to the ones on the master component sheet and list the price along with a formula to do the equation below, then I'd like to be able to drag that formula to the other 3000 parts. Mind you none of this is in order and each part has different components in it.

Example;
Part 1 - 5.00 + component 1 - 2.50 + component 2 - 6.00 x 50%

Is this possible, even if I have to use 2 or 3 functions.

Thanks in advance!

 

[BiocideJ]

I'm not 100% I understand your data, and less convinced I understand your formula (what is the -5, -2.5, -6? are those $ discounts?)
But regardless, I am going to take a stab at this and you can let me know where I am going wrong.

My assumptions:
Parts sheet is called [Parts] with Part Names in col A and corresponding prices in col B
Components sheet is called [Components] with Component Names in col A and corresponding prices in col B
A third sheet where you are calculating your results which has a given part in cell A2, a given 1st component in B2 and a given 2nd component in C2.

My proposed formula (entered in D2 of the third sheet):
=(INDEX(PARTS!$B:$B,MATCH($A2,PARTS!A:A,0))-5+INDEX(COMPONENTS!$B:$B, MATCH($B2,COMPONENTS!$A:$A,0))-2.5+INDEX(COMPONENTS!$B:$B,MATCH($C2,COMPONENTS!$A:$A,0))-6)*1.5

 

[nuiisance]

Hi BiocideJ,

The numbers were just examples. I am going to post two pictures, one with the components in column F and the prices in column G. On the next attachment I need to take those same ferrules# and use a formula to grab the data from the component sheet and do the math.

I'll give an example using the sheets.

 

[nuiisance]

Forgot to attach the images

https://ibb.co/kNCj9T
https://ibb.co/cQexUT

 

[BiocideJ]

OK, the pictures definitely help, although I'm still confused as to what the second component would be. Are you referring to the component immediately under the one reference from the part sheet?

I am going to take another stab at this, however, if I am wrong, can provide a sample calculation of what you're expecting, I think it may be the final piece to the puzzle.

entered in E2 of the part sheet
=(D2+INDEX(Component!$G:$G,MATCH(B2,Component!$F:$F,0))+INDEX(Component!$G:$G,MATCH(B3,Component!$F:$F,0)))*1.5


here is what I'm doing:
=(D2+{find M00910-04}+ {find M003300-04}) * 1.5
=($2.70+$0.50+$0.47) *1.5
=($3.67) * 1.5
=$5.51