Forum Discussion
Can you use AND / OR in an INDEX MATCH
Hi Sergei,
The second one is where the OR should be. Thanks
(A=A2 OR t=A2) AND (B = B2 AND C=C2),
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
Can you add MIN to this as well?
I have a style on two sheets and want to return the smallest date for a style if label = A or label = B.
Thank you.
If you are on Excel 365 or 2021 that could be like
=MIN( FILTER(date, (label="A")+(label="B") ) )Better if you give bit more details how is your data structured (better with screenshot or sample file) and indicate on which Excel you are.
SergeiBaklan I read thiis old example and it seems to have worked. But, all I needed was a guide to use just OR in MATCHes (the addition of ANDs in the example got me confused on the brackets and 1/zeros 🙂 I need a way for a user to enter a dashboard cell with any of 3 simple texts - and for whatever they enter be MATCHed against 3 columns (B:B engine codes, or C:C job codes, or D:D part number) - if there's ANY mach - it is passed to INDEX to find the generic Account ID from A:A column. Many Thanks for any simple guidance JW
Assuming values in these 3 columns are always different (i.e. engine code never could be equal job code, etc.) variant of the formula is
=INDEX(E3:E14,MATCH(1,INDEX( (B3:B14=H3)+(C3:C14=H3)+(D3:D14=H3),0),0))Hi Sergei, What does the INDEX function inside the MATCH function signify and How can we use INDEX inside MATCH or INDEX function?
Thanks Sergei!
