Forum Discussion

Adrian Hernandez's avatar
Adrian Hernandez
Copper Contributor
Oct 16, 2017

Help with formula

Hi there,

 

I made a basic stock inventory spreadsheet for the family granite business, but my dad is wanting a column in it to show the value of the offcuts left from jobs. Here is a picture off the layout off the Offcuts sheet

I have added a number of the different stone colours to a sheet on the spreadsheet.

 

 

I need help making a formula that will work out the cost of a piece of stone. In the Costs sheet the Cost column shows the price in £ per square metre for the differet colours of stone. In the Offcuts sheet the m2 column show the square metre size of each piece. I need it to recognise if a colour in a row on the Offcuts sheet matches a colour in the Cost sheet, to multiply the Cost for that colour by the m2 value in the Offcuts sheet. This gives the Cost value for the Offcuts sheet. 

 

I've probably explained that terribly but hope someone can help.

 

Thanks

Formulas & Functions
Need Help

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 17, 2017

    Hi Adrian,

     

    Better to use Excel tables, for your ranges that could be like (for cell I2)

    =IFERROR(INDEX(Cost!$D$1:$D$500,MATCH(C2,Cost!$A$1:$A$500,0))*G2,0)

    MATCH finds the row number in Cost sheets for matched colour and INDEX returns the cost from that row.

     

     

Resources