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:

(image with the first sheet - I haven't got enough rep to add pictures yet

And here's what the second sheet looks like:

layout of second sheet

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?

  • ..!A:E,2,0)*VL...,3,0)
    – TheMaster
    May 18 '18 at 12:39

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.

  • Perfect! This works, but I don't know why it works 😄 I've got a bit of reading to do about INDEX and MATCH.
    – user29357
    May 20 '18 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.