I'm trying to build a spreadsheet in Google Sheets to calculate a list of charges that should be oncharged to child companies (expenses are paid for by a holding company, and oncharged).
My idea for this was to create a list of expenses, and indicate what % of the expense applied to each company.
I'd then create a separate sheet (as a 'tab') for each company, and do a =FILTER
to find items where the % of the expense allocated to that company is greater than zero. I'd then =VLOOKUP
the item found earlier, to find the exact total to charge.
Here's what I've got:
And here's what the second sheet looks like:
My formula for the FILTER
is:
=FILTER('Paid by Parent Company'!A3:A1000, 'Paid by Parent Company'!C3:C1000 > 0)
My formula for the VLOOKUP
is:
=VLOOKUP(A3, 'Paid by Parent Company'!A:E, 2) * VLOOKUP(A3, 'Paid by Parent Company'!A:E, 3)
I'm getting the error #N/A
in cell B3 (the first VLOOKUP
cell), but all the other cells below it with the same formula are working.
How can I prevent/solve this error?
Best Answer
It looks like for each Child Company you have a unique invoice sheet - i.e. the lookup formula doesn't need to work out for itself which column of costs to look at because you typed in '3'. That makes the solution easier.
Use INDEX-MATCH rather than VLOOKUP. It's more robust and also quicker once you get very large spreadsheets.
In B3:
=INDEX('Paid By Parent Company'!$C$3:$C$6,MATCH($A3,'Paid By Parent Company'!$A$3:$A$6,0)) * INDEX('Paid By Parent Company'!$B$3:$B$6,MATCH($A3,'Paid By Parent Company'!$A$3:$A$6,0))
For the other invoices, change the range reference of $C$3:$C$6 to $D$3:$D$6 etc.
Also, check that B3 and C3 are both formatted as numbers not text; that could be killing your formula.