kakehavata
New Member
- Joined
- Mar 29, 2021
- Messages
- 24
- Office Version
- 2019
- 2016
- 2013
- Platform
- Windows
Hi guys.
I have created a list which shows all values that correspond to certain criteria using an array formula. It works perfectly:
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E"), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")
I want to add another condition in the IF function, namely that a certain roman numeral will appear in a given range, namely all odd numbers between 1 and 7 (I, III, V, VII). This is what I tried to do:
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*OR($D$8:$D$385="I",$D$8:$D$385="III",$D$8:$D$385="V",$D$8:$D$385="VII"), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")
This doesn't seem to be working - I am getting the same list, although some values in the D column have other roman numerals (such as II or IV).
I would also like to take into account all values which have two or more roman numerals, one of which is (in this example) an odd number (value of cells like this would be ="III-IV", "V-VI" or "I/III/V/VII").
Can you help me?
Thank you very much!
I have created a list which shows all values that correspond to certain criteria using an array formula. It works perfectly:
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E"), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")
I want to add another condition in the IF function, namely that a certain roman numeral will appear in a given range, namely all odd numbers between 1 and 7 (I, III, V, VII). This is what I tried to do:
=IFERROR(INDEX($B$8:$B$385,SMALL(IF(($AX$8:$AX$385=1)*($A$8:$A$385="E")*OR($D$8:$D$385="I",$D$8:$D$385="III",$D$8:$D$385="V",$D$8:$D$385="VII"), ROW($AX$8:$AX$385)-ROW($AX$8)+1),ROWS($AX$1:$AX1))),"")
This doesn't seem to be working - I am getting the same list, although some values in the D column have other roman numerals (such as II or IV).
I would also like to take into account all values which have two or more roman numerals, one of which is (in this example) an odd number (value of cells like this would be ="III-IV", "V-VI" or "I/III/V/VII").
Can you help me?
Thank you very much!