Consider I have 2 tables which are in same sheet.

Table 1 : Sales Table 2 : Production

In Sales table I need to calculate the profit column.

Its data will be picked from Production cost table based on the Type and Size from Sales table.

Suppose I have sold 5000 ml 1 qty Groundnut so first it should search for the production cost for 5000ml ground nut in production table.

If I get the above I will manage the remaining thing.

In the current example if I sold 5000ml qty of groundnut then it manufacturing cost should pe extract in the column so that I can minus it with my sales price.

Production cost table :

Sales Table :

CodePudding user response：

You need INDEX/MATCH function.

=INDEX($B$3:$D$7,MATCH(F3,$A$3:$A$7,0),MATCH(G3,$B$2:$D$2,0))