excel formula to select column values based on two conditions-CodePudding

I want to select the values column values corresponding to 0% and Bravo, so I want the values 0.939958, 0,067266 etc. Could someone help me out to select the values? I have tried this:

=CHOOSE(MATCH((L2=B2:J2)*(L3=B3:J3);0);B4:B21;C4:C21;D4:D21;E4:E21;F4:F21;G4:G21;H4:H21;I4:I21;J4:J21)

But this did not work..

Kind regards.

CodePudding user response：

Use INDEX and add two MATCHes together:

=INDEX($B$4:$J$21,0,MATCH(0%,$B$2:$J$2) MATCH("Bravo",$B$3:$D$3,0)-1)

This works because the Arizona,Bravo,Charlie are a repeating pattern. So the first Match returns the first starting place 1,4,7 and then the second returns either 1,2,3. We add them together and subtract one to return the correct column.

The 0 in the second criterion of the INDEX tells the INDEX to return the full column. You can then decide what row you want by replacing that with the relative row number desired

You can then change the 0% and "Bravo" to whatever you want.

Your local settings may require the replacement of , with ;.

CodePudding user response：

With ms365, try:

Formula in K3:

=INDEX(FILTER(A3:I12,A2:I2=K2),0,1 K1*2)

Note that I just used RANDARRAY() to fill the matrix with sample data.