To do this, use the VLOOKUP or XLOOKUP function. The formula for cell D5 is: =VLOOKUP(A5, $A$5:$C$7, 3, FALSE) * $B$2. It multiplies the department's share by the total fund amount.

Make sure the sum of all shares in the table equals 1 to distribute the entire budget without remainder. For modern versions of Excel, it is more convenient to use XLOOKUP: =XLOOKUP(A5, $A$5:$A$7, $C$5:$C$7, 0) * $B$2.

Such calculations often require working with dates and numbers. To avoid errors, check how to correctly fix date format in Excel.

🔗 Source: excel.rf