从多个匹配的行返回值 [英] return values from multiple matching rows
问题描述
首先，如果可能，我想做这个没有 VB，所以我不必经历教授接收者如何启用宏的麻烦。
现在，我相信我想做的很简单，但答案可能是复杂的公式。我试图在新列中列出来自两个其他列中匹配值的行中指定列的值。听起来很棘手，我确定，但一个例子应该是非常有帮助的...
说我有以下数据：
------------------
| sts | pos |再见
------------------
| 0 | QB | 8 |
| 2 | WR | 3 |
| 2 | QB | 10 |
| 0 | QB | 4 |
| 2 | QB | 7 |
| 0 | WR | 11 |
| 2 | WR | 9 |
| 2 | QB | 5 |
------------------
bye C>正。换句话说，从上面的源数据我想看到以下结果集：
------ --------------------
| pos | bye |
--------------------------
| QB | 10 | 7 | 5 | |
| WR | 3 | 9 | | |
--------------------------
...因为这些是$ code> sts = 2 bye >和 pos 等于结果表中相应的 pos 。
再次，如果可能，我想避免宏，只需在结果表的 bye 单元格中使用公式。
希望对你有一个刺痛的意义。谢谢！
FOLLOW-UP：
@ Richard-Morgan我尝试使用你的公式，但不能让它上班这是我的实际电子表格的屏幕截图，所以我们可以使用真正的单元格引用：
所以 sts 是 B2：B303 ， pos 是 D2：D303 和再见是 E2：E303 。那么我想列出列中的 U 通过 Y 。它看起来像你的答案，如果我足够聪明地实现它，将得到我所需要的，所以任何可以提供的帮助，让我到终点线非常感谢！
我使用一个隐藏的工作表来列出将所有 pos 和 sts 值，连接为一个值。所以...
sts | pos | bye
----------------------
2 | QB | 8
2 | RB | 5
2 | QB | 11
0 | WR | 7
。 。 。
...成为....
D | E
-----------
5 | 2QB | 8
6 | 2RB | 5
7 | 2QB | 11
8 | 0WR | 7
。 。 。
然后，我有一个阴影结果区域，模拟我的首页工作表上的结果区域。看起来像这样：
G | H |我| J | K
-----------------------------
5 | QB | | | | |
6 | RB | | | | |
7 | WR | | | | | 。 。 。
在 H5：H7 中，我有以下公式：
= IFERROR（（ADDRESS（MATCH（2& $ G5，$ D $ 5：$ D $ 305,0）+4， COLUMN（$ E5），4）），）
这将返回它找到的第一个单元格引用在以 2 开头的连接列中，以 G 列中的值结尾（例如，第5行中的公式正在寻找2QB ）
然后，在 I5中 n 7 我有以下修改公式：
= IFERROR（ADDRESS（MATCH（2& $ G5，INDIRECT（ADDRESS（ROW（INDIRECT ））+ 1,4）及 ：$ D $ &安培; MAX（305，ROW（INDIRECT（H5））+ 1）），0）+ ROW（INDIRECT（H5）），柱（E5 $）， 4），）
我修改后续列的原因是更改范围公式正在寻找其值，从之前找到的值开始。例如，使用上面的数据，在 H5 中的公式将在D5：D * n *中寻找2QB，并返回它找到的第一行，并将其附加到列E E5 。
然后，在 I5 中的公式将会从D * 6开始寻找2QB *而不是D5，在H5结果中引用的行
希望这是有道理的。
所以我在我的隐藏工作表中最终得到的是：
G | H |我| J | K
-----------------------------
5 | QB | E5 | E7 | | |
6 | RB | E6 | | | |
7 | WR | | | | | 。 。 。
然后，在我的首页工作表中，我只需获取值（由H5中的单元格引用的再见（* / code））：* n * 7使用：
= IFERROR（INDIRECT （查找！H5），）
...这给了我最终的结果： p>
G | H |我| J | K
-----------------------------
5 | QB | 8 | 11 | | |
6 | RB | 5 | | | |
7 | WR | | | | | 。 。 。
像我说的，这完全是复杂的，但它的工作原理，如果我弄清楚如何。感谢你们为我这个看似复杂的问题摆了一个秋千。我确定你的答案也很精美。
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!
OK I figured out a way to get my desired results. It isn't the cleanest or best way, but it achieves my goal of listing the results horizontally, and avoids macros or pivot tables.
I use a hidden worksheet to list out all the pos and sts values, concatenated as a single value. So...
sts | pos | bye
----------------------
2 | QB | 8
2 | RB | 5
2 | QB | 11
0 | WR | 7
. . .
...becomes....
D | E
-----------
5 | 2QB | 8
6 | 2RB | 5
7 | 2QB | 11
8 | 0WR | 7
. . .
Then, I have a "shadow" results area that mimics the results area on my front-page worksheet. It looks like so:
G | H | I | J | K
-----------------------------
5 | QB | | | | |
6 | RB | | | | |
7 | WR | | | | | . . .
In H5:H7, I have the following formula:
=IFERROR((ADDRESS(MATCH("2"&$G5,$D$5:$D$305,0)+4,COLUMN($E5),4)),"")
This returns the first cell reference it finds in the concatenated column that starts with 2 and ends with the value in column G (e.g. the formulas in row 5 are looking for "2QB").
Then, in I5:n7 I have the following modified formula:
=IFERROR(ADDRESS(MATCH("2"&$G5,INDIRECT(ADDRESS(ROW(INDIRECT(H5))+1,4)&":$d$"&MAX(305,ROW(INDIRECT(H5))+1)),0)+ROW(INDIRECT(H5)),COLUMN($E5),4),"")
The reason I modify the subsequent columns is to change the range in which the formula is looking for its value to start at the next row after the previously found value. For example, with the data above, the formula in H5 would look for "2QB" in D5:D*n*, and return the first row it finds and attach it to column E, which would be E5.
The formula in I5 would then look for "2QB" starting in D*6* instead of D5, a row after the row referenced in H5's result.
Hopefully that made sense.
So what I end up with in my hidden worksheet is this:
G | H | I | J | K
-----------------------------
5 | QB | E5 | E7 | | |
6 | RB | E6 | | | |
7 | WR | | | | | . . .
Then, on my front page worksheet, I simply get the values (the bye) referenced by the cells in H5:*n*7 using:
=IFERROR(INDIRECT(lookups!H5),"")
...which gives me my final result:
G | H | I | J | K
-----------------------------
5 | QB | 8 | 11 | | |
6 | RB | 5 | | | |
7 | WR | | | | | . . .
Like I said, it's totally convoluted, but it works, and I can always refine it later if I figure out how. :) Thanks to you who took a swing at this seemingly complex problem for me! I'm sure your answers work beautifully as well.
这篇关于从多个匹配的行返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
