IF OR in an array with SMALL

[garybusey]

I'm trying to use an IF OR in an array with a SMALL argument. Essentially, I have a series of data that will say HIGH or HIGH & LOW in a certain row in column G, and then whatever is in column A of this row will be returned by my formula. It worked great returning those with just HIGH by using this formula:

{=IF(ISERROR(INDEX($A$1:$A$1001,SMALL(IF($G$1:$G$1001="HIGH",ROW($A$1:$A$1001)),ROW(1:1)),1)),"",INDEX($A$1:$A$1001,SMALL(IF($G$1:$G$1001="HIGH",ROW($A$1:$A$1001)),ROW(1:1)),1))}


However I'm trying to get it to return what is in A if G is either HIGH or HIGH & LOW. I can't get this working with IF OR and it is driving me mad, have been trying:


{=IF(ISERROR(INDEX($A$1:$A$1001,SMALL(IF(OR($G$1:$G$1001="HIGH",$G$1:$G$1001="HIGH & LOW"),ROW($A$1:$A$1001)),ROW(1:1)),1)),"",INDEX($A$1:$A$1001,SMALL(IF(OR($G$1:$G$1001="HIGH",$G$1:$G$1001="HIGH & LOW"),ROW($A$1:$A$1001)),ROW(1:1)),1))}

But it doesn't work. I have to use arrays and SMALL as I never know what rows will be showing HIGH or HIGH & LOW as it depends on a series of varying parameters.

Any help would be massively appreciated. Thanks!

 

[Aladin Akyurek]

What is the Excel version that you use and in which cell do you enter the formula?

 

[Andrew Poulsom]

Welcome to MrExcel.

Try using addition for OR:

=IF(ISERROR(INDEX($A$1:$A$1001,SMALL(IF((($G$1:$G$1001="HIGH")+($G$1:$G$1001="LOW")),ROW($A$1:$A$1001)),ROW(1:1)),1)),"",INDEX($A$1:$A$1001,SMALL(IF((($G$1:$G$1001="HIGH")+($G$1:$G$1001="LOW")),ROW($A$1:$A$1001)),ROW(1:1)),1))

 

[garybusey]

Welcome to MrExcel.

Try using addition for OR:

=IF(ISERROR(INDEX($A$1:$A$1001,SMALL(IF((($G$1:$G$1001="HIGH")+($G$1:$G$1001="LOW")),ROW($A$1:$A$1001)),ROW(1:1)),1)),"",INDEX($A$1:$A$1001,SMALL(IF((($G$1:$G$1001="HIGH")+($G$1:$G$1001="LOW")),ROW($A$1:$A$1001)),ROW(1:1)),1))

Andrew, that works perfectly, thanks a lot, this was driving me mad! Cheers