Match either of two criteria (not both)

[TBG]

Let's say I have the following situation:

Column A has values 1-15, each repeated many times. I want a match function to return all the row numbers for which column A is a 3 or a 4. Any ideas on how to do this? (It's within an index function (which is with a standard deviation function), so not sure how I could match twice and not break my higher level nested functions)

Appreciate any help.

[Ron Coderre]

Here's one approach that returns an array of all the rows where A1:A60 equals either 3 or 4

=LARGE((A1:A60={3,4})*ROW(A1:A60),ROW(INDIRECT("1:"&SUM(COUNTIF(A1:A60,{3,4})))))

To test
...Select the cell with that formula
...Press F2
...Press F9
that will display the array.
Press ESC to see the formula again.

What do you want to do with the results?

[TBG]

Works great - giving me the row references I need. However, I'm having trouble using those within the rest of my formula.

I'm trying to return the standard deviation for all data in the rows within an array that meet those criteria (you already cleared that hump for me). Here is what I've got currently that isn't working (adapted to fit your great example):

=STDEV.P(INDEX(B10:G60, LARGE((A10:A60={3,4})*ROW(A1:A60),ROW(INDIRECT("1:"&SUM(COUNTIF(A1:A60,{3,4})))))-ROW(A9), 0))

Any ideas? It's definitely the index that isn't working - the array is returning far too few numbers for the standard deviation to look at. (Sweet trick on the F2, F9). Also, if it helps, the index function on it's own without the stdev.p is currently returning the values from the entire row of the largest row number that matches both criteria.

Also, I've never used brackets within an array formula before, do they tell excel to look for either of those criteria?

[Ron Coderre]

I haven't worked with StdDevs in a while. Can you post some sample data AND the results you want to see based on that data?
Attaching a workbook would be ideal.

F2 F9...One of my favorite "tricks"
The braces { } simply represent a hardcoded array.
Example:
A1: 3
B1: 4
C1: =MATCH(4,A1:B1,0)

• Select C1
• In the formula bar, select the A1:B1 section
• Press F9
You'll see: =MATCH(4,{3,4},0)

In an array, commas separate fields on the same "row". Semicolons separate one row from the next.

[TBG]

Sure. In this example I've got the column of data I want to match my criteria to on the left, the array in the middle, and the criteria and my attempt at a formula on the right. My only idea at this point is to write a separate index function for each column, and then instruct each of them to only look at the correct rows within the column. I know it will work, but it is really inelegant (and even worse in my larger, actual database).

Standard deviation from an array example.xlsx

Thanks for all the help.

[Ron Coderre]

You'll have to tell me if I got this ARRAY FORMULA right:

R3: =STDEV.P(IF(ISNUMBER(MATCH(ROW($A$2:$A$31),LARGE((($A$2:$A$31=$N3)+ ($A$2:$A$31=$O3)+($A$2:$A$31=$P3))*ROW($A$2:$A$31), 
ROW(INDIRECT("1:"&SUM(COUNTIF($A$2:$A$31, $N$3:$O$3))))),0)),$C$2:$L$31))

Copy R3 and paste into R4:R5

Does that help?

[TBG]

Worked perfectly, great idea. I don't follow the logic though - if you'd be willing to help me understand it so I could use the techniques in future I'd be even more grateful.

I gather that within an IF statement ISNUMBER returns true for numbers and false for all else - how does it then know to multiply those 1s and FALSEs by the row numbers within the array in [value_if_true] though? Don't quite see how the IF statement knows to use each TRUE and FALSE a number of times equal to the # of columns in the [value_if_true] array.

Similarly, does IF with the [value_if_true] set as an array always return the values within the array as a string (which is of course exactly what I wanted?

[Ron Coderre]

OK...I'll do my best, but this is definitely the deep end of the pool:

Regarding:
=STDEV.P(IF(ISNUMBER(MATCH(ROW($A$2:$A$31),LARGE((($A$2:$A$31=$N3)+ ($A$2:$A$31=$O3)+($A$2:$A$31=$P3))*ROW($A$2:$A$31), ROW(INDIRECT("1:"&SUM(COUNTIF($A$2:$A$31, $N$3:$O$3))))),0)),$C$2:$L$31))

Basically, here's what we want to do:
If a cell in Col_A matches one of the values in my list
Then use the data from that row in my STDEV function
Otherwise, ignore the numbers in that row (set each value to FALSE)

This section creates the list of rows we're interested in:
LARGE((($A$2:$A$31=$N3)+($A$2:$A$31=$O3)+($A$2:$A$31=$P3))*ROW($A$2:$A$31),ROW(INDIRECT("1:"&SUM(COUNTIF($A$2:$A$31,$N$3:$O$3)))))

Since each cell can only contain one value, we need to test for each.
That's what this section does:
(($A$2:$A$31=$N3)+($A$2:$A$31=$O3)+($A$2:$A$31=$P3))

Let's use cell A14 for the example, so the new formula is this:
=STDEV.P(IF(ISNUMBER(MATCH(ROW($A$14),LARGE((($A$14=$N3)+($A$14=$O3)+($A$14=$P3))*ROW($A$14),ROW(INDIRECT("1:"&SUM(COUNTIF($A$14,$N$3:$O$3))))),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(ROW($A$14),LARGE((($A$14=3)+($A$14=4)+($A$14=0))*ROW($A$14),ROW(INDIRECT("1:"&SUM(COUNTIF($A$14,$N$3:$O$3))))),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,LARGE((($A$14=3)+($A$14=4)+($A$14=0))*14,ROW(INDIRECT("1:"&SUM(COUNTIF(3,{3,4,0}))))),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,LARGE((1+0+0)*14,ROW(INDIRECT("1:"&SUM(COUNTIF(3,{3,4,0}))))),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,LARGE(14,ROW(INDIRECT("1:"&SUM({1,0,0})))),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,LARGE(14,ROW(INDIRECT("1:"&1))),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,LARGE(14,ROW(1:1)),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,LARGE(14,1),0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(MATCH(14,14,0)),$C$14:$L$14))

=STDEV.P(IF(ISNUMBER(1),$C$14:$L$14))

=STDEV.P(IF(TRUE,$C$14:$L$14))

So the values in WOULD be used.

If the formula was applied to Row_2, the formula would resolve this way:
=STDEV.P(IF(ISNUMBER(MATCH(ROW($A$2),LARGE((($A$2=$N3)+($A$2=$O3)+($A$2=$P3))*ROW($A$2),ROW(INDIRECT("1:"&SUM(COUNTIF($A$2,$N$3:$O$3))))),0)),$C$2:$L$2))

=STDEV.P(IF(ISNUMBER(MATCH(ROW($A$2),LARGE((($A$2=3)+($A$2=4)+($A$2=0))*2,ROW(INDIRECT("1:"&SUM(COUNTIF($A$2,{3,4,0}))))),0)),$C$2:$L$2))

=STDEV.P(IF(ISNUMBER(MATCH(2,LARGE((0+0+0)*2,ROW(INDIRECT("1:"&SUM({0,0,0})))),0)),$C$2:$L$2))

=STDEV.P(IF(ISNUMBER(MATCH(2,LARGE((0,ROW(INDIRECT("1:0"))),0)),$C$2:$L$2))

This example actually falls apart here, because there is no reference like: ROW(1:0).
so we'll pretend it's ROW(1:1), ok?
=STDEV.P(IF(ISNUMBER(MATCH(2,LARGE(0,1),0)),$C$2:$L$2))

=STDEV.P(IF(ISNUMBER(MATCH(2,0,0)),$C$2:$L$2))

=STDEV.P(IF(FALSE,$C$2:$L$2))

=STDEV.P(FALSE)...and none of the values in $C$2:$L$2 would be used.


That same logic is applied in the original formula to each row in the range.