I am exporting a full TB file from sage accounting and then trying to select
certain lines/data into a new format. The format of the new workbook has been
set up with all of the P&L account numbers and cost centres. I have been
trying to use 'vlookup' to select the account name and cost centre and if the
combination of account and cost centre is matched in the TB workbook it
returns the relevant figures against the nominal ledger codes.
i just can not get it to return any sensible figures: As i do not know how
to formulate meeting two criteria and if this matches return a value from
column 7.
Is this because there are gaps in my TB report (and it is 8500 lines long)
or that each account number is repeated across 31 cost centres?
Does anyone know what to enter in the formula as tried 'IF' 'index'
'match'etc with nothing working as i am really unsure what to select first.

Array formula:

=INDEX(relevant_figures,MATCH(1,(account_numbers=A CCT)*
(cost_centers=CC),0))

Remarks:
relevant_figures is the range containing the relevant information that
you want to bring
account_numbers is the range parallel to relevant_figures. ACCT is the
account you want to look up
same for cost_centers and CC

This is an *array* formula: commit with Shift+Ctrl+Enter.

HTH
Kostis Vezerides

On Apr 29, 6:52*pm, cas wrote:
I am exporting a full TB file from sage accounting and then trying to select
certain lines/data into a new format. The format of the new workbook has been
set up with all of the P&L account numbers and cost centres. I have been
trying to use 'vlookup' to select the account name and cost centre and if the
combination of account and cost centre is matched in the TB workbook it
returns the relevant figures against the nominal ledger codes.
i just can not get it to return any sensible figures: As i do not know how
to formulate meeting two criteria and if this matches return a value from
column 7. *
Is this because there are gaps in my TB report (and it is 8500 lines long)
or that each account number is repeated across 31 cost centres?
Does anyone know what to enter in the formula as tried 'IF' 'index'
'match'etc with *nothing working as i am really unsure what to select first. *