Google-sheets – Substitute for Lookup() in Google Spreadsheets

google sheets

I am making a bookkeeping sheet.

The aim is to create rules, that search imported fields from our bank statement, and if a match is found automatically fill the adjoining cells with the rules data.

example:

Data:

HEAD - Imported Field       - Payees (autofill) - Account (autofill)
ROW1 - payment to JASON U   - from rule below   - from rule below
ROW2 - 234322 MERCHANT FEES - from rule below   - from rule below

Rules:(separate sheet)

HEAD - Matched Term - Payee     - Account
ROW1 - JASON U      - Jason Ure - Drawings
ROW2 - MERCHANT     - CBA       - Fees

We have this working in Excel now, but need to get it working in Google Spreadsheets.
Anyone know how to convert this? (see in skydrive/excel below)

Best Answer

At this time, the LOOKUP function is available in the New Google Sheets.

Short answer

Yes, it's possible. To do that you will require to use the Edit or Change Google Sheets events of Google Apps Scripts.

Extended answer

You can find a code example in the answer from Jacob Jan Tuinstra to Updating logging sheet from another sheet doesn't work after inserting code offset

References

Google-sheets – Advanced lookup + sum

I came up with this solution.

Formulae

(1) =TRANSPOSE(ARRAY_ROW(JAN!A1,FEB!A1,MAR!A1))
(2) =ARRAYFORMULA(VLOOKUP(A1:A3,VLOOKUP!A1:B3,2,0))
(3) =QUERY(A1:B3, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Totals'")

Screenshots

(1)
enter image description here

(2)
enter image description here

(3)
enter image description here

Explained

The ARRAY_ROW formula allows for easy preparation of a range. The TRANSPOSE formula re-arranges the row into a column position. This way all entries are nicely presented (1). The VLOOKUP and the ARRAYFORMULA will efficiently look for all corresponding numbers in the VLOOKUP sheet (2). The QUERY formula is used to perform the totals calculation (3).

Example

I've created an example file for you: Google Sheets advanced lookup + sum