Looking for more efficient method of dividing actual spends

Hi, I'm attaching a generic sheet with a sample of what I'm trying to do. I'm definitely a beginner when it comes to Excel.

Sample-Coding-Spreadsheet.xlsx

In the "Jan" workbook, I'll be adding invoices with actual expenditures. The company I work for has four companies. We split the expenses between the companies and depending on the type expenditure (i.e., advertising, supplies, etc.), the percentages vary.

In the "CodingRates" workbook, I have the different types of spend and each column represents a company and what percentage of the expense they are paying for.

In the Jan workbook (in columns J, K, L, and M) I would like the Actual to be multiplied by the percentage in the CodingRates workbook. So for row 2, the spend was Advertising and Company 1 would pay for 65%, Company 2 would pay for 10% and Company 3 would pay for 25%.

I tried using an IF statement to grab the correct percentage based on Coding, but it is not a very efficient method since I have to manually update the formula for every cell.

=IF(I2="Advertising", [@Actual]*'CodingRates'!B2, (if(I2="Supplies", [@Actual]*'CodingRates'!B3, (if(I2="Subscription", [@Actual]*'CodingRates'!B4, 0)))))

The problem is if I have to include more Coding types, then I have to keep adjusting these formulas manually. I also can't cut and paste the formula since the cells from the CodingRates sheet are static and never change. I'm sure there has to be a much easier method than this.

Any help would be greatly appreciated.

Thanks,
-Sharon

hi Sharon, welcome to the forum. this in J2 will give you the percentage:
=VLOOKUP($I2,CodingRates!$A$2:$E$4,MATCH(J$1,CodingRates!$A$1:$E$1,0),0)

you can then multiply the numbers with it. i suppose it's G2?
=VLOOKUP($I2,CodingRates!$A$2:$E$4,MATCH(J$1,CodingRates!$A$1:$E$1,0),0)*$G2

Hmm, so I tried applying this formula to my actual spreadsheet. Some of the columns are a little different and the Coding Rates table is larger (A2:E9) , but now I'm getting a #N/A error.

The new formula looks like this (without the multiplication for now). The only changes I made are in bold. $K2 is the Coding column and N$1 is the Company 1 column

=VLOOKUP($K2,CodingRates!$A$2:$E$9,MATCH(N$1,CodingRates!$A$1:$E$1,0),0)

What am I doing wrong?

PS - The "Jan" workbook in my actual spreadsheet is formatted as a pivot table, I'm not sure if that makes a difference.

Hard to say without looking at a spreadsheet (Go Advanced> Manage Attachments) but maybe

Try to find out which part is giving you the error
=MATCH(N$1,CodingRates!$A$1:$E$1,0)? maybe
I would think that should be
=VLOOKUP($K2,CodingRates!$A$2:$E$9,MATCH(N$1,CodingRates!$B$1:$E$1,0),0)

Hello,

A typical VLOOKUP will look like this
=VLOOKUP(Lookup_Value, Table_Array, Column_No, [Not_exact_match])
What it does is "Vertically lookup" your Lookup_Value in the column number Column_No, and return exact match or approximate match (basing on Lookup_Type, if omitted it will be "True" by default, meaning approximate looking).
In your formula, however, you use MATCH to find the Column_No, thus you have another formula, hence it may have a different range to lookup for value. MATCH function was used to find the column where your value can be in, then return the distance from whichever column it starts (for example, if it starts at column A, and found it at C, it will return 3; if it starts at D, and found it at F, it will still return 3).

Hope this help.

you should make sure N1:Q1 of "Jan" is the same as B1:E1 of "CodingRates". 1st one currently is "ABC Cost ". 2nd one is "ABC". if you really have it in this format & do not wish to change, this will help:
=VLOOKUP($K2,CodingRates!$A$2:$E$9,MATCH(LEFT(N$1,FIND(" ",N$1)-1),CodingRates!$A$1:$E$1,0),0)*$J2

what i added in red extracts the text of "ABC Cost " before the 1st space

also, if you need to mask the errors for row 8 & below:
=IF([@Coding]="","",VLOOKUP($K2,CodingRates!$A$2:$E$9,MATCH(LEFT(N$1,FIND(" ",N$1)-1),CodingRates!$A$1:$E$1,0),0)*$J2)