Thursday, July 17, 2014

yogi_Pull Into 'Payables' All The Unpaid Invoiced From data In 'Itemized Expenses'

                                  Google Spreadsheet   Post  #1697
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jul-17-2014
post by Cathy Samuel: (!mydiscussions/docs/UmPVMUgD3gQ)
Convert Array Formula from Excel to Google Spreadsheet
I am trying to convert a formula in Excel to work in Google Spreadsheet, but keep getting a parse error.  What the formula is supposed to do is look up for all unpaid invoices and list them on a separate sheet called "Payables". All invoices are on the sheet "Itemized Expenses".  Column C has the name of payee; Column D has the amount to be paid and Column E has the check number.  If column E is empty then it is assumed that the invoice has not been paid. 

This is the formula that works in Excel:
=IFERROR(INDEX('Itemized Expenses'!C$2:C$284,SMALL(IF('Itemized Expenses'!$E$2:$E$284<=Payables!$A$2,ROW('Itemized Expenses'!C$2:C$284)-ROW('Itemized Expenses'!C$2)+1),ROWS('Itemized Expenses'!C$2:'Itemized Expenses'!C2))),"")

This is my attempt at converting it in Google, where I get parse error:
=ARRAYFORMULA(INDEX('Itemized Expenses'!C$2:C$284,SMALL(IF('Itemized Expenses'!$E$2:$E$284<0,ROW('Itemized Expenses'!C$2:C$284)-ROW('Itemized Expenses'!C$2)+1),ROWS('Itemized Expenses'!C$2:'Itemized Expenses'!C2)),""))

I have been struggling with this for a week now, I really hope I can get some help.

Google Spreadsheet has functions like FILTER and QUERY that are much better suited for a problem like yours