I have been struggling to find a more automated way in order to make a calculation the past few months and I would like your help. I will give you a more detailed description of what I want to achieve.

Table 1 is in file Filename001.xlsx and Sheet1

A | B | C | |

1 | X1 | Y1 | Z1 |

2 | X1 | Y2 | Z2 |

3 | X2 | K1 | Z3 |

4 | X3 | M1 | Z4 |

5 | X3 | M2 | Z5 |

6 | X2 | K2 | Z6 |

7 | X1 | Y3 | Z7 |

8 | X2 | K3 | Z8 |

9 | X1 | Y4 | Z9 |

<tbody>

</tbody>

Table 2 is in file Filename002.xlsx and Sheet2

A | B | |

1 | Y1 | R1 |

2 | Y2 | R2 |

3 | Y3 | R3 |

4 | Y4 | R4 |

<tbody>

</tbody>

Table 3 is in file Filename002.xlsx and Sheet3

A | |

1 | X1 |

2 | X2 |

3 | X3 |

4 | X4 |

<tbody>

</tbody>

I write the following code in file Filename001 and Sheet1

=(C1+C2+C7+C9)/((C1/(VLOOKUP(B1,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C2/(VLOOKUP(B2,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C7/(VLOOKUP(B7,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE)))+(C9/(VLOOKUP(B9,[Filename002.xlsx]Sheet2!$A$1:$Z$4,2,FALSE))))

Basically I locate X1 manually each time. What I want to do is to replace the above code with an automated one. So far, the only thing I found a way to replace was

(C1+C2+C7+C9)

with

(SUMIF($A1:$A9,[Filename002.xlsx]Sheet3!$A$1,$C1:$C9))

Is it possible what I'm trying to do over here?

Any ideas are welcomed. Thank you in advance for your time.