Why is my =VLOOKUP function returning #N/A for the first cell?

Question

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?

Answer 1

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.