excel - return values from multiple matching rows

Question

First off, I'd like to do this without VB if possible, so I don't have to go through the hassle of teaching recipients how to enable macros.

Now, I believe what I'd like to do is simple, but the answer may be complex formula-wise. I'm trying to list out in new columns the values from a specified column in rows which have matching values from two other columns. Sounds tricky I'm sure, but an example should help immensely...

Say I have the following data:

 ------------------
| sts | pos  | bye |
 ------------------
| 0   | QB   | 8   |
| 2   | WR   | 3   |
| 2   | QB   | 10  |
| 0   | QB   | 4   |
| 2   | QB   | 7   |
| 0   | WR   | 11  |
| 2   | WR   | 9   |
| 2   | QB   | 5   |
 ------------------

That's my source. I want to list out the bye value from all rows that have sts = 2, for each respective pos. In other words, from the source data above I'd want to see the following result set:

 --------------------------
| pos | byes               |
 --------------------------
| QB  | 10  | 7  | 5  |    |
| WR  | 3   | 9  |    |    |
 --------------------------

...because those are the bye values in the rows with sts = 2 and pos equal to the corresponding pos in the result table.

Again, I'd like to avoid macros if possible, and just use a formula in the bye cells of the results table.

Hopefully that makes enough sense for you to take a stab at it. Thanks!

FOLLOW-UP:

@Richard-Morgan I attempted to use your formula but can't get it to work. Here is a screenshot of my actual spreadsheet so we can use real cell references:

So sts is B2:B303, pos is D2:D303, and bye is E2:E303. So then I'd like to list out the byes in columns U thru Y. It looks like your answer, if I'm smart enough to implement it, will get me what I need, so any assistance you can provide to get me to the finish line is greatly appreciated!

Answer 1

Something along the lines of the following could be used:

{=INDEX(tbl, SMALL(IF(COUNTIF(G$3, $A$2:$A$9)
 *COUNTIF(G$4, $B$2:$B$9), ROW(tbl)-MIN(ROW(tbl))+1), ROW($C1)), COLUMN($C1))}

where the A column is sts, B column is pos, and C column is bye. The variable tbl is the range of data (not the headers). G$3 would be the sts filter and G$4 is the pos filter.

Copy the array formula DOWN to find all the matching byes; #NUM! will appear after finding no more matches. If this bothers your users, you can add an ISERROR or a tricky conditional format that makes the text white on white.

You can then copy over the formula to the next column and enter new filter values.

            G   H
sts Search  2   2
pos Search  QB  WR

            10  3
            7   9
            5   #NUM!
            #NUM!#NUM!

If your users are comfortable with pivot tables, using them would be much easier, I would think.

EDIT Making the formula "transpose" is a bit tricky and I am not having any breakthrough on how to fix that. However, if you want to manually edit the column formulas, here is what you want.

(I made the assumption that S is the sts filter. Maybe you're just doing a count there, but I didn't see where you enter the filter for sts. If S isn't the sts filter, update the formulas to point to where sts is 2 or whatever.)

U2:

{=INDEX(tbl, SMALL(IF(COUNTIF($S2, $B$2:$B$303)*COUNTIF($R2, $D$2:$D$303), 
 ROW(tbl)-MIN(ROW(tbl))+1), ROW($D$1)), COLUMN($D$1))}

V2:

{=INDEX(tbl, SMALL(IF(COUNTIF($S2, $B$2:$B$303)*COUNTIF($R2, $D$2:$D$303), 
ROW(tbl)-MIN(ROW(tbl))+1), ROW($D$2)), COLUMN($D$2))}

etc.

This allows the cells to be copied down.

I am sure there is a way to INDIRECT the ROW/COLUMN, but I ran out of time to look at this at the moment.

EDIT 2 If you put a simple number increment somewhere, let's say U1 has 1, V1 has 2, W1 has 3, etc., you could use the following:

{=INDEX(tbl, SMALL(IF(COUNTIF($S2, $B$2:$B$9)*COUNTIF($R2, $D$2:$D$9), 
ROW(tbl)-MIN(ROW(tbl))+1), U$1), COLUMN($D$1))}

This will copy down and across.