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 in an OR function for the name in column A. I will add this name in Column T. In other words the match is correct if column A or T match A2 and COL B=B2 and COL C=C2 also match the criteria I tried using the + to add T criteria but gave me a 0=INDEX('Rebate report'!A:A,MATCH(1,('Rebate report'!A:A=A2)*('Rebate report'!B:B=B2)*('Rebate report'!C:C=C2)+('Rebate report'!A:A=T2),0),1) Thanks for the help!

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)))

Hi David, More exactly(A=A2 OR A=T2) AND (B = B2 AND C=C2), that was my misprint. In this case array formula could be=IFERROR(INDEX('Rebate report'!A:A,MATCH(1,((('Rebate report'!A:A=A2)+('Rebate report'!A:A=T2))>0)*('Rebate report'!B:B=B2)*('R'!C:C=C2),0),1),0)First multiplier in MATCH imitates OR condition, next are with AND

joostbr1965 Does column B of the Ledger Schedule sheet contain the entry numbers? If so, try =IFNA(IF(LEFT('Ledger Schedule'!$B$4,3)="EXP", INDEX(Expenses!$B:$B,MATCH(1,(Expenses!$B:$B>'Ledger Schedule'!$B9)*(Expenses!I:I='Ledger Schedule'!$B$4),0)), INDEX(Income!$B:$B,MATCH(1,(Income!$B:$B>'Ledger Schedule'!$B9)*(Income!I:I='Ledger Schedule'!$B$4),0))), "") Change Income to the real name of the expense sheet if necessary.

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!

kalyansp That's the trick for the Excel which doesn't support dynamic arrays, e.g. Excel 2016. If in such Excel we use formula like =INDEX(A:A, MATCH( 1, (B:B=2)*(C:C=3), 0 ) ) here (B:B=2)*(C:C=3) is an array and we shall use above formula as an array one, i.e. to enter with Ctrl+Shift+Enter. However, with INDEX( (B:B=2)*(C:C=3), 0 ) which returns exactly the same array we may use formula =INDEX(A:A, MATCH( 1, INDEX( (B:B=2)*(C:C=3), 0 ), 0 ) ) as regular one, i.e. enter it simply with Enter. Above is not required for the Excel which supports dynamic arrays (Excel 365). And on practice please don't use entire columns as in above sample, that significantly affects the performance. Structured tables, dynamic ranges or fixed ranges instead.

Can you use AND / OR in an INDEX MATCH | Microsoft Community Hub

SergeiBaklan
MVP
Oct 27, 2017
Hi David,

If it returns 0 that's something in your figures. Formula works like

if ABC or T matches it return some number
if ABC and T matches it returns #N/A
if no one of ABC and T matches it returns #N/A
- David
  Copper Contributor
  Oct 28, 2017
  Thanks Sergei
  
  Your second statement is not what I am trying to do.
  I want it to give a name in Column from the Rebate Report if:
  
  if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name
  if A=A2 AND T=A2 AND B=B2 AND C=C2 return a cell ref for name. This should return a ref and not NA. This seemed different from what you said it would do in the formula.
  If A not match A2 AND T also not match A2 OR B not match B2 OR C not match C2 then return NA.
  
  Let me know if this makese sense. Thanks.
  - SergeiBaklan
    MVP
    Oct 28, 2017
    Hi David,
    
    Just to clarify first one, where is OR?
    A=A2 OR (t=A2 AND B = B2 AND C=C2)
    (A=A2 OR t=A2) AND (B = B2 AND C=C2),
    ...
    
    David wrote:
    if A=A2 OR t=A2 AND B = B2 AND C=C2 return a cell ref for name

Forum Discussion

Can you use AND / OR in an INDEX MATCH

Share

Resources