List based on multiple criteria, including roman numerals as values

[kakehavata]

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!

 

[kakehavata]

The formula I posted is exactly the same as the one Marcelo posted.

Ah, sorry, I didn't apply it where it should be. Then the problem remains the same: this works only for single numerals, not for multiple...

 

[Marcelo Branco]

Ah, sorry, I didn't apply it where it should be. Then the problem remains the same: this works only for single numerals, not for multiple...

hmm... i think you should replace each condition
($D$8:$D$385="I")
by
(ISNUMBER(SEARCH("-I-","-"&$D$8:$D$385&"-")))

And add > 0
((condition1)+...+(condition4)>0)

Again: Not Tested

M.

 

[kakehavata]

hmm... i think you should replace each condition
($D$8:$D$385="I")
by
(ISNUMBER(SEARCH("-I-","-"&$D$8:$D$385&"-")))

And add > 0
((condition1)+...+(condition4)>0)

Again: Not Tested

M.

Thanks a lot! For some reason it seems to be working without the >0, not with it. I will experiment with it a bit more before I can see that it's fully working.

 

[Marcelo Branco]

Thanks a lot! For some reason it seems to be working without the >0, not with it

You're welcome.
In fact, >0 in this case is superfluous, but the formula should have worked with or without it.

M.