Forum Discussion
Can you use AND / OR in an INDEX MATCH
Hi I have am array formula that looks like this: =INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2),0),1) which works. I want to add...
First, i agree with Peter that this should have been a new thread.
Second, I would strongly recommend you consider re-doing you workbook to have ALL entries in ONE table and then have other tabs as reports (expense report just filters all the expense lines, Income report filters all the income lines, account reports ...)
Third, I recommend you make use of the the 'Format as a Table' function on the Home menu. This will let you refer to data based on a Table reference instead of columns so instead of Income!$B:$B it might be Income[Voucher Number]. This makes it easier to work with, easier to read (once you get used to it) and then excel only looks at the data rows instead of the entire column of the worksheet (over a million rows). For the below I will pretend you format the Income table as a table called INCOME and the Expense table as a table called EXPENSE.
So a solution to the existing, with Excel 365 you should be able to use:
=SORT(VSTACK(FILTER(INCOME, INCOME[Account Number]=$B$4, ""),FILTER(EXPENSE, EXPENSE[Account Number]=$B$4)))
Second, I would strongly recommend you consider re-doing you workbook to have ALL entries in ONE table and then have other tabs as reports (expense report just filters all the expense lines, Income report filters all the income lines, account reports ...)
Third, I recommend you make use of the the 'Format as a Table' function on the Home menu. This will let you refer to data based on a Table reference instead of columns so instead of Income!$B:$B it might be Income[Voucher Number]. This makes it easier to work with, easier to read (once you get used to it) and then excel only looks at the data rows instead of the entire column of the worksheet (over a million rows). For the below I will pretend you format the Income table as a table called INCOME and the Expense table as a table called EXPENSE.
So a solution to the existing, with Excel 365 you should be able to use:
=SORT(VSTACK(FILTER(INCOME, INCOME[Account Number]=$B$4, ""),FILTER(EXPENSE, EXPENSE[Account Number]=$B$4)))
My apologies for replying to an old thread.
The tables have been formatted as and the formula has been really helpful, it's working now with a bit of tweaking and testing. Really appreciate all the help! Have a great day!
The tables have been formatted as and the formula has been really helpful, it's working now with a bit of tweaking and testing. Really appreciate all the help! Have a great day!