Finding more than 1 instance of text in a list

[sue01]

I'm setting up a workbook in Excel 2007 which imports the year's data from Sage Accounts and uses that data to produce P&L Account, Balance Sheet and Cashflow acctuals and budget to the end of the year. My problem is with a floating account in the Balance Sheet, ie an account which can be either and asset or a liability depending on whether it has a debit or credit balance. I am using MATCH and VLOOKUP to find the heading on the Balance Sheet in the input sheet and a second MATCH to find "Current Liabilities" and return the appropriate figure for the month in question from either the assets or the liabilities. However, the problem with htis is that within the same month you may have a movement during the period in the assets with a year to date balance in the liabilities or vice versa, in which case the heading will appear twice in the same list in the input sheet, but the MATCH will only pick up the first instance of it, ie the asset, and the liability will not be reported. Does anyone have a way around this, ie a way of finding a second MATCH, or a way of using the position of "Current Liabilities" to to define the range for the MATCH. The position of "Current Liabilities" within the list will change month to month and company to company.

 

[BrianB]

I wonder if =SUMIF() could work.

 

[sue01]

Thanks for the idea Brian. Unfortunately SUMIF needs some criteria to work on to differentiate between the two instances of the same account name and there is nothing apart from their positions on the Balance Sheet; both, for instance, will be a positive number.

I have now managed to find a work around to this problem by reporting the asset figure less the liability figure to a separate range, which then gives a positive result for assets or a negative result for liabilities which can then be picked up and put into the correct place in the final report.

Sue